-- 1. INSERT and UPDATE trigger on referencing table.
 
 -- Disallow any insert or update if the foreign key title_id 
 
 -- 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 that 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 the referenced table titles will set to NULL the
 
 --  referencing titleauthor rows
 
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 set to a
NULL title_id as a result
of a delete to the titles table',  10 ,  1 , @counter)
GO