Implementing cascade updates with instead of triggers
Razvan Socol
IF EXISTS (SELECT * FROM sysobjects WHERE name='Conversions')
DROP TABLE Conversions
IF EXISTS (SELECT * FROM sysobjects WHERE name='Units')
DROP TABLE Units
CREATE TABLE Units (
Code varchar(3) PRIMARY KEY,
UnitDescription nvarchar(50) NOT NULL UNIQUE
)
CREATE TABLE Conversions (
Source varchar(3) NOT NULL
CONSTRAINT FK_Source FOREIGN KEY REFERENCES Units,
Destination varchar(3) NOT NULL
CONSTRAINT FK_Destination FOREIGN KEY REFERENCES Units,
Ratio float NOT NULL,
PRIMARY KEY (Source, Destination),
CHECK (Source<>Destination)
)
INSERT INTO Units VALUES ('M', 'Meter')
INSERT INTO Units VALUES ('KM', 'Kilometer')
INSERT INTO Units VALUES ('IN', 'Inch')
INSERT INTO Units VALUES ('G', 'Gram')
INSERT INTO Units VALUES ('KG', 'Kilogram')
INSERT INTO Units VALUES ('T', 'Ton')
INSERT INTO Conversions VALUES ('KM', 'M', 1000)
INSERT INTO Conversions VALUES ('IN', 'M', 0.0254)
INSERT INTO Conversions VALUES ('KG', 'T', 0.001)
-- and so on...
I want to implement cascade update and cascade deletes for the two
foreign keys. I cannot simply do this:
ALTER TABLE Conversions DROP CONSTRAINT FK_Source
ALTER TABLE Conversions ADD CONSTRAINT FK_Source
FOREIGN KEY (Source) REFERENCES Units
ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE Conversions DROP CONSTRAINT FK_Destination
ALTER TABLE Conversions ADD CONSTRAINT FK_Destination
FOREIGN KEY (Destination) REFERENCES Units
ON DELETE CASCADE ON UPDATE CASCADE
Because SQL Server says "Introducing FOREIGN KEY constraint
'FK_Destination' on table 'Conversions' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or
modify other FOREIGN KEY constraints."
So I wrote the following trigger:
CREATE TRIGGER Units_xD_CascadeDelete ON Units
INSTEAD OF DELETE
AS
SET NOCOUNT ON
DELETE Conversions
WHERE Source IN (SELECT Code FROM deleted)
OR Destination IN (SELECT Code FROM deleted)
DELETE Units
WHERE Code IN (SELECT Code FROM deleted)
Which works just fine. However, to implement cascade updates it's more
complicated. I wrote the following trigger:
CREATE TRIGGER Units_xU_CascadeUpdate ON Units
INSTEAD OF UPDATE
AS
IF UPDATE(Code) AND @@ROWCOUNT>1 BEGIN
RAISERROR ('Multi-row updates for the PK are not allowed !',16,1)
ROLLBACK
RETURN
END
SET NOCOUNT ON
IF UPDATE(Code) BEGIN
UPDATE Conversions SET Source=i.Code
FROM Conversions c
INNER JOIN deleted d ON c.Source=d.Code
CROSS JOIN inserted i
UPDATE Conversions SET Destination=i.Code
FROM Conversions c
INNER JOIN deleted d ON c.Destination=d.Code
CROSS JOIN inserted i
UPDATE Units
SET Code=i.Code, UnitDescription=i.UnitDescription
FROM Units u
INNER JOIN deleted d ON u.Code=d.Code
CROSS JOIN inserted i
END
ELSE BEGIN
UPDATE Units SET UnitDescription=i.UnitDescription
FROM Units u INNER JOIN inserted i ON u.Code=i.Code
END
But it doesn't work, i.e. when I execute something like:
UPDATE Units SET Code='TON' WHERE Code='T'
I get the following error: "UPDATE statement conflicted with COLUMN
FOREIGN KEY constraint 'FK_Destination'. The conflict occurred in
database 'Teste', table 'Units', column 'Code'."
The cause is clear: I cannot write the new value 'TON' in the
Conversions table, because it doesn't exist yet in the Units table. So
I wrote the following:
ALTER TRIGGER Units_xU_CascadeUpdate ON Units
INSTEAD OF UPDATE
AS
IF UPDATE(Code) AND @@ROWCOUNT>1 BEGIN
RAISERROR ('Multi-row updates for the PK are not allowed !',16,1)
ROLLBACK
RETURN
END
SET NOCOUNT ON
IF UPDATE(Code) BEGIN -- there is only 1 row (we checked above)
IF (SELECT Code FROM inserted)<>(SELECT code FROM deleted) BEGIN
UPDATE Units SET UnitDescription='(This will be deleted)'
WHERE Code IN (SELECT Code FROM deleted)
INSERT INTO Units (Code, UnitDescription)
SELECT Code, UnitDescription FROM inserted
UPDATE Conversions SET Source=i.Code
FROM Conversions c
INNER JOIN deleted d ON c.Source=d.Code
CROSS JOIN inserted i
UPDATE Conversions SET Destination=i.Code
FROM Conversions c
INNER JOIN deleted d ON c.Destination=d.Code
CROSS JOIN inserted i
DELETE Units WHERE Code IN (SELECT Code FROM deleted)
END
ELSE BEGIN
UPDATE Units SET UnitDescription=i.UnitDescription
FROM Units u INNER JOIN inserted i ON u.Code=i.Code
END
END
ELSE BEGIN
UPDATE Units SET UnitDescription=i.UnitDescription
FROM Units u INNER JOIN inserted i ON u.Code=i.Code
END
My question is: is there a better way to do this ? I know I can drop
the FK constraint completely (or use NOCHECK) and enforce it through
another trigger, on the Conversions table (but I don't want to do this
right now, because I like constraints more than triggers: I feel safer
when I have a constraint). I know that I can use a surrogate key (e.g.
IDENTITY) as the PK (but I don't want to do this either, in this case).
And one more thing: the solution should work on SQL Server 2000.
Razvan
Alejandro Mesa
Let us suppose we do not have the fk constraint, how can we identify the
related rows in tables inserted and deleted when we are changing the pk?
The is no way, unless you force them to do it one row at a time, and this is
what is causing the problem because now we can not identity what are the new
values corresponding to a specific row in both tables when multirows update
occurs.
The only thing that can help is not allowing them to change the primary key
in the parent table?
AMB
Alejandro Mesa
help.
AMB
Razvan Socol
> inserted and deleted when we are changing the pk?
If the primary key is changing and there are multiple rows, the only
way to identify which rows are related is to use another unique key
that is not changing. As you can see, I have another unique key
(UnitDescription), but this may change too. So I made the restriction
that the PK may be updated only one row at a time. That's OK for me in
this case. I was asking if there is any way to simplify (or otherwise
enhance) the trigger.
> [...] the use of an identity column could help.
a) if the identity column is the PK, then the whole problem of
cascading updates dissapears. But, as I already wrote, I don't want to
do this in this case (I want to use the varchar(3) Code as the PK).
b) if I add another identity column (as another unique key, not as the
PK), indeed it helps for identifying which inserted rows relate to
which deleted rows. But I still can't change the values for the FK from
the other table in the "instead of" trigger, because the new values
aren't in the referenced table yet. If I want to add them before
changing those values, I have a problem with the other unique
constraint (UnitDescription). In the case of single-row update, I
avoided this problem by setting the UnitDescription for the old code to
another value, but for a multi-row update this workaround becomes
unusable. So, unless you suggest a better workaround for this issue, I
will keep the single-row restriction when the PK is changed (anyway, I
don't think that I will ever need to change the PK for more than one
row at a time).
Razvan
Alejandro Mesa
> PK), indeed it helps for identifying which inserted rows relate to
> which deleted rows. But I still can't change the values for the FK from
> the other table in the "instead of" trigger, because the new values
> aren't in the referenced table yet.
Then it yields to not having a fk constraint and checking RI using triggers,
something I always try to avoid.
Another thing could be not allowing to change both columns (Code,
UnitDescription ) at the same time, so we can use them interchangeably as
unique values to identify rows.
AMB
Razvan Socol
> checking RI using triggers, something I always try to avoid.
Between the two following variants:
(a) not having a FK constraint and checking RI only with triggers
and
(b) keeping the FK constraint and writing an "instead of" trigger that
supports only single-row updates
I choose (b), in this case.
> Another thing could be not allowing to change both columns
> (Code, UnitDescription ) at the same time, so we can use
> them interchangeably as unique values to identify rows.
Please write an "insead of" trigger for cascading updates in this
scenario (only the Code column is changed, but multi-row; of course, do
not drop the FK).
Razvan
Alejandro Mesa
> scenario (only the Code column is changed, but multi-row; of course, do
> not drop the FK).
My fault, forget about it.
Using an identity column in parent table as the pk solves this problem if
you want to allow them to change column [Code].
AMB
--CELKO--
duplicates.
DROP TABLE SourceUnits, DestinationUnits, Conversions;
CREATE TABLE SourceUnits
(source_unit_code VARCHAR(3) PRIMARY KEY,
unit_description VARCHAR(15) NOT NULL UNIQUE);
CREATE TABLE DestinationUnits
(destination_unit_code VARCHAR(3) PRIMARY KEY,
unit_description VARCHAR(15) NOT NULL UNIQUE);
CREATE TABLE Conversions
(source VARCHAR(3) NOT NULL
CONSTRAINT FK_Source FOREIGN KEY
REFERENCES SourceUnits(source_unit_code)
ON DELETE CASCADE ON UPDATE CASCADE,
destination VARCHAR(3) NOT NULL
CONSTRAINT FK_Destination FOREIGN KEY
REFERENCES DestinationUnits(destination_unit_code)
ON DELETE CASCADE ON UPDATE CASCADE,
ratio FLOAT NOT NULL,
PRIMARY KEY (source, destination),
CHECK (source <> destination) );
INSERT INTO SourceUnits VALUES ('M', 'Meter');
INSERT INTO SourceUnits VALUES ('KM', 'Kilometer');
INSERT INTO SourceUnits VALUES ('IN', 'Inch');
INSERT INTO SourceUnits VALUES ('G', 'Gram');
INSERT INTO SourceUnits VALUES ('KG', 'Kilogram');
INSERT INTO SourceUnits VALUES ('T', 'Ton');
--might want to hide this in a Trigger or stored procedure
INSERT INTO DestinationUnits SELECT * FROM SourceUnits;
INSERT INTO Conversions VALUES ('KM', 'M', 1000);
INSERT INTO Conversions VALUES ('IN', 'M', 0.0254);
INSERT INTO Conversions VALUES ('KG', 'T', 0.001);
SELECT * FROM Conversions;
SELECT * FROM DestinationUnits;
SELECT * FROM SourceUnits;
To delete or update a unit of measure, I need to hit both columns DRI
actions, which will mean you should use stored procedures for the IUD
operations.,
DELETE FROM SourceUnits WHERE source_unit_code = 'M';
DELETE FROM DestinationUnits WHERE destination_unit_code = 'M';
Hugo Kornelis
(snip)
>My question is: is there a better way to do this ?
Hi Razvan,
Here's how I would change your trigger. I try to avoid the proprietary
UPDATE FROM syntax when the ANSI standard versions can be used. I also
use variables to reduce the number of times inserted and deleted are
queried. And I have made a slight modification to the logic, to better
match what I consider the "logical" order (YMMV).
I considered aligning all BEGIN with corresponding END (as I believe
maintainable code should look like), but decided against it, as this is
apparently your prefered style <shrug>.
CREATE TRIGGER Units_xU_CascadeUpdate ON Units
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF @@ROWCOUNT = 0 RETURN
IF UPDATE(Code) BEGIN
IF @@ROWCOUNT>1 BEGIN
RAISERROR ('Multi-row updates for the PK are not allowed
!',16,1)
ROLLBACK
RETURN
END
DECLARE @old varchar(3), @new varchar(3)
SET @old = (SELECT Code FROM deleted)
SET @new = (SELECT Code FROM inserted)
IF @new <> @old BEGIN
INSERT INTO Units (Code, UnitDescription)
SELECT Code, UnitDescription FROM inserted
UPDATE Conversions
SET Source = CASE WHEN Source = @old THEN @new
ELSE Source END,
Destination = CASE WHEN Destination = @old THEN @new
ELSE Destination END
WHERE Source = @old
OR Destination = @old
DELETE Units WHERE Code = @old
END
ELSE BEGIN
UPDATE Units
SET UnitDescription =
(SELECT UnitDescription
FROM inserted)
WHERE Code = @old
END
END
ELSE BEGIN
UPDATE Units SET UnitDescription=i.UnitDescription
FROM Units u INNER JOIN inserted i ON u.Code=i.Code
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Razvan Socol
I appreciate your comments, however there are are a few problems with
your code:
1. The verification of @@ROWCOUNT is incorrect, because the SET NOCOUNT
ON sets @@ROWCOUNT to 0, and the trigger always returns without doing
anything; moreover, even if we move SET NOCOUNT ON to the second line,
the error is not raised if we update the PK for multiple rows, because
the IF also resets @@ROWCOUNT, and we get a "Subquery returned more
than 1 value". Therefore, I made the following modifications to the
first lines of the trigger:
[...]
DECLARE @rowcnt int
SET @rowcnt=@@ROWCOUNT
IF @rowcnt=0 RETURN
SET NOCOUNT ON
IF UPDATE(Code) BEGIN
IF @rowcnt>1 BEGIN
[...]
2. The "INSERT INTO Units" fails with the error "Violation of UNIQUE
KEY constraint 'UQ__Units__580A5672'", because the UnitDescription for
the inserted row is the same as the one for the old row. I made a
workaround for this problem by executing a "UPDATE Units SET
UnitDescription='(This will be deleted)'..." before the INSERT, but you
have removed it. I put it back, modified a bit:
[...]
UPDATE Units
SET UnitDescription='(This will be deleted)'
WHERE Code=@old
[...]
On the other hand, I like your ideas about using variables to reduce
the number of times inserted and deleted are queried and about updating
both the Source and the Destination in a single statement. I also agree
about using the ANSI syntax for the UPDATE.
Thank you again for your remarks,
Razvan
Hugo Kornelis
>Hello, Hugo
>
>I appreciate your comments, however there are are a few problems with
>your code:
>
>1. The verification of @@ROWCOUNT is incorrect, because the SET NOCOUNT
>ON sets @@ROWCOUNT to 0, and the trigger always returns without doing
>anything; moreover, even if we move SET NOCOUNT ON to the second line,
>the error is not raised if we update the PK for multiple rows, because
>the IF also resets @@ROWCOUNT, and we get a "Subquery returned more
>than 1 value". Therefore, I made the following modifications to the
>first lines of the trigger:
(snip)
Hi Razvan,
Ah yes, of course. Not sure how I could have missed that. I guess this
beyond doubt that I didn't test my code before posting... <blush>.
>2. The "INSERT INTO Units" fails with the error "Violation of UNIQUE
>KEY constraint 'UQ__Units__580A5672'", because the UnitDescription for
>the inserted row is the same as the one for the old row.
(snip)
And this proves I didn't pay very close attention to the DDL you posted
<blush again>. I did wonder why you had the extra update statement in
your code. Now I know.
>On the other hand, I like your ideas about using variables to reduce
>the number of times inserted and deleted are queried and about updating
>both the Source and the Destination in a single statement. I also agree
>about using the ANSI syntax for the UPDATE.
>
>Thank you again for your remarks,
Happy to help!