SQL trig_ref_actions Code Library

-- 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