-- 1. INSERT and UPDATE trigger on referencing table.
-- Disallow any insert or update if the foreign key title_id
-- here in the referencing table titleauthor does not match
-- the primary key title_id in the referenced table titles.
CREATE TRIGGER INS_UPD_titleauthor
ON titleauthor
FOR INSERT, UPDATE
AS
-- Do any rows exist in the inserted table that do not have
-- a matching ID in titles?
IF EXISTS
(SELECT * FROM inserted WHERE inserted.title_id NOT IN
(SELECT titles.title_id FROM titles) )
BEGIN
RAISERROR('No matching title found. Statement will be
aborted.', 16, 1)
ROLLBACK TRAN
END
GO
-- 2. If primary key in referenced table titles is changed,
-- update any rows in titleauthor to the DEFAULT value.
-- This implements ON UPDATE SET DEFAULT. Notice this trigger could
-- be easily changed to set the column to NULL instead of DEFAULT,
-- which would implement ON UPDATE SET NULL.
CREATE TRIGGER UPD_titles
ON titles
FOR UPDATE
AS
DECLARE @counter int
IF UPDATE(title_id)
BEGIN
UPDATE titleauthor
SET titleauthor.title_id=DEFAULT
FROM titleauthor, deleted
WHERE titleauthor.title_id=deleted.title_id
SET @COUNTER=@@ROWCOUNT
-- If the trigger resulted in modifying rows of
-- titleauthor, raise an informational message
IF (@counter > 0)
RAISERROR('%d rows of titleauthor were updated to
DEFAULT title_id as a result of an update to titles table',
10, 1, @counter)
END
GO
-- 3. DELETE of referenced table titles will set to NULL referencing
-- titleauthor rows a
CREATE TRIGGER DelCascadeTrig
ON titles
FOR DELETE
AS
DECLARE @counter int
UPDATE titleauthor
SET title_id = NULL
FROM titleauthor, deleted
WHERE titleauthor.title_id=deleted.title_id
SET @counter=@@ROWCOUNT
IF (@counter > 0)
RAISERROR('%d rows of titleauthor were were set to a
NULL title_id as a result
of a delete to the titles table', 10, 1, @counter)
GO
/* Make changes to structure of titles and titleauthor tables */
ALTER TABLE titles NOCHECK CONSTRAINT ALL
go
ALTER TABLE sales NOCHECK CONSTRAINT ALL
go
ALTER TABLE titleauthor NOCHECK CONSTRAINT ALL
go
ALTER TABLE titleauthor DROP CONSTRAINT UPKCL_taind
go
ALTER TABLE titleauthor ALTER COLUMN title_id tid NULL
go
ALTER TABLE titleauthor ADD CONSTRAINT UPKCL_taind UNIQUE (au_id, title_id)
go
ALTER TABLE roysched NOCHECK CONSTRAINT ALL
go
ALTER TABLE publishers NOCHECK CONSTRAINT ALL
go
-- Add one dummy row to titles
INSERT titles VALUES ('AA0000','No Such Title','unknown','0',0,0,0,0,'No notes','Jun 12 1996 12:00AM')
go
-- Make that dummy row the default title_id in titleauthor
ALTER TABLE titleauthor ADD CONSTRAINT Def_title DEFAULT 'AA0000' FOR title_id
go
/* Now test out the triggers */
-- Invalid insert to titleauthor
INSERT titleauthor (au_id,title_id,au_ord,royaltyper) VALUES ('172-32-1176','XXXXXX',1,100)
go
select @@rowcount
go
-- Valid insert to titleauthor
INSERT titleauthor (au_id,title_id,au_ord,royaltyper) VALUES ('172-32-1176','BU1032',1,100)
go
select @@rowcount
go
-- Invalid multirow update to titleauthor
UPDATE titleauthor SET title_id = substring(title_id,1,4)+convert(char(1),au_ord)
go
-- Valid multirow update to titleauthor
UPDATE titleauthor SET title_id = title_id
go
-- Show that delete will set to NULL. Find titleauthor rows for title TC7777.
-- Update the title_id in those rows to NULL
SELECT * FROM titleauthor where title_id='PS2091'
OR title_id IS NULL
DELETE titles where title_id='PS2091'
SELECT * FROM titleauthor where title_id='PS2091'
OR title_id IS NULL
go
-- Show that an update to the Primary Key of the
-- referenced table will cause all of the Foreign Keys
-- that had previously referenced it to be
-- set to the DEFAULT title_id, which is 'AA0000'
DECLARE @au_id varchar(11)
SELECT @au_id=au_id FROM titleauthor where title_id='TC4203'
SELECT au_id,title_id FROM titleauthor where au_id=@au_id
UPDATE titles set title_id='WW9999' where title_id='TC4203'
SELECT au_id,title_id FROM titleauthor where au_id=@au_id
go
CREATE TRIGGER UpdCascadeTrig1
ON titles
AFTER UPDATE
AS
DECLARE @num_affected int, @title_id varchar(11),
@old_title_id varchar(11)
SET @num_affected=@@ROWCOUNT
IF (@num_affected=0) -- No rows affected, so nothing to do
RETURN
IF UPDATE(title_id)
BEGIN
IF (@num_affected=1)
BEGIN
SELECT @title_id=title_id FROM inserted
SELECT @old_title_id=title_id FROM deleted
UPDATE titleauthor
SET title_id=@title_id
FROM titleauthor
WHERE titleauthor.title_id=@old_title_id
SELECT @num_affected=@@ROWCOUNT
RAISERROR ('Cascaded update in titles of Primary Key
from %s to %s to %d rows in titleauthor', 10, 1,
@old_title_id, @title_id, @num_affected)
END
ELSE
BEGIN
RAISERROR ('Cannot update multiple Primary Key values
in a single statement due to Cascading Update
trigger in existence.', 16, 1)
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER UpdCascadeTrig2
ON titles
FOR UPDATE
AS
DECLARE @num_distinct int, @num_affected int,
@title_id varchar(11)
SET @num_affected=@@ROWCOUNT
IF (@num_affected=0) -- No rows affected, so nothing to do
RETURN
IF UPDATE(title_id)
BEGIN
SELECT @num_distinct=COUNT(DISTINCT title_id) FROM inserted
IF (@num_distinct=1)
BEGIN
-- Temporarily make it return just one row
SET ROWCOUNT 1
SELECT @title_id=title_id FROM inserted
SET ROWCOUNT 0 -- Revert ROWCOUNT back
UPDATE titleauthor
SET titleauthor.title_id=@title_id
FROM titleauthor, deleted
WHERE titleauthor.title_id=deleted.title_id
SELECT @num_affected=@@ROWCOUNT
RAISERROR ('Cascaded update of Primary Key to value in
titles to %d rows in titleauthor', 10, 1,
@title_id, @num_affected)
END
ELSE
BEGIN
RAISERROR ('Cannot cascade a multirow update that
changes title_id to multiple different values.', 16, 1)
ROLLBACK TRANSACTION
END
END
