March 27, 2012 at 11:04 am
I have a requirement to add 3 foreign keys with cascade update to one table. The key fields are cumulative (there's a hierarchy involved). We aren't using identities because additions to the tables may take place online in the production system or be sent through SSIS with ID values already generated and referenced by other rows in the package. We've searched for an immutable key--there is none. These names are the true natural keys, and they're mutable.
I've attached sample DDL to illustrate the problem:
CREATE TABLE top_level (
top_level_id INT NOT NULL PRIMARY KEY
)
CREATE TABLE level_a (
top_level_id INT NOT NULL
REFERENCES top_level(top_level_id)
ON DELETE CASCADE,
level_a_name VARCHAR(50) NOT NULL,
PRIMARY KEY (top_level_id, level_a_name)
)
CREATE TABLE level_b (
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NOT NULL,
level_b_name VARCHAR(50) NOT NULL,
PRIMARY KEY (top_level_id, level_a_name, level_b_name),
FOREIGN KEY (top_level_id, level_a_name)
REFERENCES level_a(top_level_id, level_a_name)
ON DELETE CASCADE ON UPDATE CASCADE
)
CREATE TABLE level_c (
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NOT NULL,
level_b_name VARCHAR(50) NOT NULL,
level_c_name VARCHAR(50) NOT NULL
PRIMARY KEY (top_level_id, level_a_name, level_b_name, level_c_name),
FOREIGN KEY (top_level_id, level_a_name, level_b_name)
REFERENCES level_b(top_level_id, level_a_name, level_b_name)
ON DELETE CASCADE ON UPDATE CASCADE
)
CREATE TABLE test
(
test_id int identity not null primary key,
top_level_id INT NOT NULL REFERENCES top_level(top_level_id),
level_a_name VARCHAR(50) NULL,
level_b_name VARCHAR(50) NULL,
level_c_name VARCHAR(50) NULL,
FOREIGN KEY (top_level_id, level_a_name)
REFERENCES level_a(top_level_id, level_a_name)
, --ON UPDATE CASCADE,
FOREIGN KEY (top_level_id, level_a_name, level_b_name)
REFERENCES level_b(top_level_id, level_a_name, level_b_name)
, --ON UPDATE CASCADE,
FOREIGN KEY (top_level_id, level_a_name, level_b_name, level_c_name)
REFERENCES level_c(top_level_id, level_a_name, level_b_name, level_c_name)
, --ON UPDATE CASCADE
)
The Test table represents users who may be located at any level of the hierarchy. The table has a single-column immutable primary key, but it's not an identity in our system (I did that here for simplicity). The top level ids are immutable, but the level a, b and c names are not.
The DDL permits one update cascade (I've commented all of them), but that's not useful. The rest of them have the "multiple cycle" issue.
I've dealt with the cycling issue with cascade delete. I write a procedure that deletes referenced rows before target rows. But how can cascade update be implemented in a procedure? Or a trigger?
I can't update either table due to the foreign key. If I insert a new row, update references, and delete the row, the lower hierarchy doesn't cascade update like it should.
I'm struggling for an order of operations that won't require dropping and recreating keys on every update.
This SQL (which mostly fails) shows some tactics I've tried:
-- add some test data
INSERT top_level VALUES (1)
INSERT level_a VALUES (1, 'g'), (1, 'h'), (1, 'i')
INSERT level_b VALUES (1, 'g', 'r')
INSERT test (top_level_id, level_a_name)
VALUES (1, 'g'), (1, 'g'), (1, 'h'), (1, NULL), (1, NULL)
INSERT test (top_level_id, level_a_name, level_b_name)
VALUES (1, 'g', 'r')
-- try to update one first, then the next one
-- attempt to update level_a (fails)
UPDATE level_a SET level_a_name = 'g1'
WHERE top_level_id = 1 AND level_a_name = 'g'
-- attempt to update test first (fails)
UPDATE test SET level_a_name = 'g1'
WHERE top_level_id = 1 AND level_a_name = 'g'
-- try to insert the new value, update references, then delete the old one
-- insert the new value
INSERT level_a VALUES (1, 'g1')
-- update test (fails due to level_b)
UPDATE test
SET level_a_name = 'g1'
WHERE top_level_id = 1 AND level_a_name = 'g'
-- try to update the lower hierarchy (which would cascade update)
-- update level b (fails due to referencing row in Test)
UPDATE level_b
SET level_a_name = 'g1'
WHERE top_level_id = 1 AND level_a_name = 'g'
-- NOW WHAT???
I can use either a dedicated procedure or a trigger. Given how complicated this is getting, I think a trigger might be advisable so that if a developer is doing ad hoc updates (in dev db, we don't do that to production) he doesn't go nuts trying to solve this problem all over again.
March 27, 2012 at 11:11 am
Sometimes asking other people makes the answer show up in my mind. The posted code below works. I think I can stuff it into an INSTEAD OF UPDATE trigger with some minor modifications to account for possible multi-row updates.
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @x TABLE (test_id int not null,
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NULL,
level_b_name VARCHAR(50) NULL,
level_c_name VARCHAR(50) NULL
)
INSERT @x
SELECT test_id, top_level_id, level_a_name, level_b_name, level_c_name
FROM Test
WHERE top_level_id = 1 AND level_a_name = 'g'
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
FROM test t
JOIN @x x ON x.test_id = t.test_id
UPDATE level_a SET level_a_name = 'g1'
WHERE top_level_id = 1 AND level_a_name = 'g'
UPDATE @x
SET level_a_name = 'g1'
UPDATE test
SET level_a_name = x.level_a_name, level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
COMMIT
March 27, 2012 at 3:21 pm
I think I said "stuff it into an update trigger." Yeah, right. Well, for anyone who's interested, these are the final triggers. I had to remove the cascade updates from all the involved tables (if you're looking at the prior DDL). The triggers work (I think) but they could use a bit of touch up.
CREATE TRIGGER level_a_upd_trg ON level_a INSTEAD OF UPDATE
AS
DECLARE @x TABLE (
test_id int not null,
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NULL,
level_b_name VARCHAR(50) NULL,
level_c_name VARCHAR(50) NULL
)
DECLARE @b-2 TABLE (
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NOT NULL,
level_b_name VARCHAR(50) NOT NULL
)
DECLARE @C TABLE (
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NOT NULL,
level_b_name VARCHAR(50) NOT NULL,
level_c_name VARCHAR(50) NOT NULL
)
DECLARE @new_top_level_id INT
DECLARE @new_level_a_name VARCHAR(50)
IF UPDATE (top_level_id) AND NOT UPDATE(level_a_name)
BEGIN
DECLARE @count INT
SELECT @count = COUNT(DISTINCT top_level_id)
FROM inserted
IF @count > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_a" not allowed.', 16, 1)
END
ELSE
BEGIN
SET @new_top_level_id = (SELECT DISTINCT top_level_id FROM inserted)
-- top level id has changed on multiple rows
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
DELETE level_c
OUTPUT deleted.* INTO @C
FROM level_c c
JOIN deleted d ON d.top_level_id = c.top_level_id AND d.level_a_name = c.level_a_name
DELETE level_b
OUTPUT deleted.* INTO @b-2
FROM level_b b
JOIN deleted d ON d.top_level_id = b.top_level_id AND d.level_a_name = b.level_a_name
UPDATE level_a SET top_level_id = @new_top_level_id
FROM level_a a
JOIN deleted d ON d.top_level_id = a.top_level_id AND d.level_a_name = a.level_a_name
UPDATE @x SET top_level_id = @new_top_level_id
FROM @x
UPDATE @b-2 SET top_level_id = @new_top_level_id
FROM @b-2 b
INSERT level_b
SELECT *
FROM @b-2
UPDATE @C SET top_level_id = @new_top_level_id
FROM @C c
INSERT level_c
SELECT *
FROM @C
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
END
IF UPDATE(level_a_name)
BEGIN
IF @@ROWCOUNT = 1
BEGIN
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
DELETE level_c
OUTPUT deleted.* INTO @C
FROM level_c c
JOIN deleted d ON d.top_level_id = c.top_level_id AND d.level_a_name = c.level_a_name
DELETE level_b
OUTPUT deleted.* INTO @b-2
FROM level_b b
JOIN deleted d ON d.top_level_id = b.top_level_id AND d.level_a_name = b.level_a_name
UPDATE level_a SET top_level_id = i.top_level_id, level_a_name = i.level_a_name
FROM level_a a
JOIN deleted d ON d.top_level_id = a.top_level_id AND d.level_a_name = a.level_a_name
CROSS JOIN inserted i
UPDATE @x SET top_level_id = i.top_level_id, level_a_name = i.level_a_name
FROM @x
CROSS JOIN inserted i
UPDATE @b-2 SET top_level_id = i.top_level_id, level_a_name = i.level_a_name
FROM @b-2 b
CROSS JOIN inserted i
INSERT level_b
SELECT *
FROM @b-2
UPDATE @C SET top_level_id = i.top_level_id, level_a_name = i.level_a_name
FROM @C c
CROSS JOIN inserted i
INSERT level_c
SELECT *
FROM @C
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_a" not allowed.', 16, 1)
END
END
GO
CREATE TRIGGER level_b_upd_trg ON level_b INSTEAD OF UPDATE
AS
DECLARE @x TABLE (
test_id int not null,
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NULL,
level_b_name VARCHAR(50) NULL,
level_c_name VARCHAR(50) NULL
)
DECLARE @C TABLE (
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NOT NULL,
level_b_name VARCHAR(50) NOT NULL,
level_c_name VARCHAR(50) NOT NULL
)
IF NOT UPDATE(level_b_name) AND (UPDATE(top_level_id) OR UPDATE(level_a_name))
BEGIN
DECLARE @count INT
SELECT @count = COUNT(*)
FROM (SELECT 1 x FROM inserted
GROUP BY top_level_id, level_a_name) a
IF @count > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_a" not allowed.', 16, 1)
END
ELSE
BEGIN
DECLARE @new_top_level_id INT
DECLARE @new_level_a_name VARCHAR(50)
SET @new_top_level_id = (SELECT DISTINCT top_level_id FROM inserted)
SET @new_level_a_name = (SELECT DISTINCT level_a_name FROM inserted)
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
AND d.level_b_name = t.level_b_name
DELETE level_c
OUTPUT deleted.* INTO @C
FROM level_c c
JOIN deleted d ON d.top_level_id = c.top_level_id AND d.level_a_name = c.level_a_name
AND d.level_b_name = c.level_b_name
UPDATE level_b SET top_level_id = @new_top_level_id, level_a_name = @new_level_a_name
FROM level_b b
JOIN deleted d ON d.top_level_id = b.top_level_id AND d.level_a_name = b.level_a_name
AND d.level_b_name = b.level_b_name
UPDATE @x SET top_level_id = @new_top_level_id, level_a_name = @new_level_a_name
FROM @x
UPDATE @C SET top_level_id = @new_top_level_id, level_a_name = @new_level_a_name
FROM @C c
INSERT level_c
SELECT *
FROM @C
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
END
IF UPDATE(level_b_name)
BEGIN
IF @@ROWCOUNT = 1
BEGIN
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
AND d.level_b_name = t.level_b_name
DELETE level_c
OUTPUT deleted.* INTO @C
FROM level_c c
JOIN deleted d ON d.top_level_id = c.top_level_id AND d.level_a_name = c.level_a_name
AND d.level_b_name = c.level_b_name
UPDATE level_b SET top_level_id = i.top_level_id, level_a_name = i.level_a_name,
level_b_name = i.level_b_name
FROM level_b b
JOIN deleted d ON d.top_level_id = b.top_level_id AND d.level_a_name = b.level_a_name
AND d.level_b_name = b.level_b_name
CROSS JOIN inserted i
UPDATE @x SET top_level_id = i.top_level_id, level_a_name = i.level_a_name,
level_b_name = i.level_b_name
FROM @x
CROSS JOIN inserted i
UPDATE @C SET top_level_id = i.top_level_id, level_a_name = i.level_a_name,
level_b_name = i.level_b_name
FROM @C c
CROSS JOIN inserted i
INSERT level_c
SELECT *
FROM @C
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_b" not allowed.', 16, 1)
END
END
GO
CREATE TRIGGER level_c_upd_trg ON level_c INSTEAD OF UPDATE
AS
DECLARE @x TABLE (
test_id int not null,
top_level_id INT NOT NULL,
level_a_name VARCHAR(50) NULL,
level_b_name VARCHAR(50) NULL,
level_c_name VARCHAR(50) NULL
)
IF NOT UPDATE(level_c_name) AND (UPDATE(top_level_id) OR UPDATE(level_a_name) OR UPDATE(level_b_name))
BEGIN
DECLARE @count INT
SELECT @count = COUNT(*)
FROM (SELECT 1 x FROM inserted
GROUP BY top_level_id, level_a_name, level_b_name) a
IF @count > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_c" not allowed.', 16, 1)
END
ELSE
BEGIN
DECLARE @new_top_level_id INT
DECLARE @new_level_a_name VARCHAR(50)
DECLARE @new_level_b_name VARCHAR(50)
SET @new_top_level_id = (SELECT DISTINCT top_level_id FROM inserted)
SET @new_level_a_name = (SELECT DISTINCT level_a_name FROM inserted)
SET @new_level_b_name = (SELECT DISTINCT level_b_name FROM inserted)
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
AND d.level_b_name = t.level_b_name
UPDATE level_b SET top_level_id = @new_top_level_id, level_a_name = @new_level_a_name,
level_b_name = @new_level_b_name
FROM level_b b
JOIN deleted d ON d.top_level_id = b.top_level_id AND d.level_a_name = b.level_a_name
AND d.level_b_name = b.level_b_name
UPDATE @x SET top_level_id = @new_top_level_id, level_a_name = @new_level_a_name,
level_b_name = @new_level_b_name
FROM @x
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
END
IF UPDATE(level_c_name)
BEGIN
IF @@ROWCOUNT = 1
BEGIN
UPDATE test
SET level_a_name = NULL, level_b_name = NULL, level_c_name = NULL
OUTPUT deleted.test_id, deleted.top_level_id, deleted.level_a_name,
deleted.level_b_name, deleted.level_c_name INTO @x
FROM test t
JOIN deleted d ON d.top_level_id = t.top_level_id AND d.level_a_name = t.level_a_name
AND d.level_b_name = t.level_b_name AND d.level_c_name = t.level_c_name
UPDATE level_c SET top_level_id = i.top_level_id, level_a_name = i.level_a_name,
level_b_name = i.level_b_name, level_c_name = i.level_c_name
FROM level_c c
JOIN deleted d ON d.top_level_id = c.top_level_id AND d.level_a_name = c.level_a_name
AND d.level_b_name = c.level_b_name AND d.level_c_name = c.level_c_name
CROSS JOIN inserted i
UPDATE @x SET top_level_id = i.top_level_id, level_a_name = i.level_a_name,
level_b_name = i.level_b_name, level_c_name = i.level_c_name
FROM @x
CROSS JOIN inserted i
UPDATE test
SET top_level_id = x.top_level_id, level_a_name = x.level_a_name,
level_b_name = x.level_b_name, level_c_name = x.level_c_name
FROM test t
JOIN @x x ON t.test_id = x.test_id
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Multi-row update on table "level_c" not allowed.', 16, 1)
END
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply