Insert trigger example

When you insert a new foreign key row, make sure the foreign key matches a primary key. The trigger should check for joins between the inserted rows (using the inserted table) and the rows in the primary key table, and then roll back any inserts of foreign keys that do not match a key in the primary key table.

The following trigger compares the title_id values from the inserted table with those from the titles table. It assumes that you are making an entry for the foreign key and that you are not inserting a null value. If the join fails, the transaction is rolled back.

create trigger forinsertrig1 
on salesdetail 
for insert 
as 
if (select count(*) 
    from titles, inserted 
    where titles.title_id = inserted.title_id) !=
    @@rowcount 
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "No, the title_id does not exist in
    titles." 
  end  
/* Otherwise, allow it. */
else
  print "Added! All title_id’s exist in titles."

@@rowcount refers to the number of rows added to the salesdetail table. This is also the number of rows added to the inserted table. The trigger joins titles and inserted to determine whether all the title_ids added to salesdetail exist in the titles table. If the number of joined rows, which is determined by the select count(*) query, differs from @@rowcount, then one or more of the inserts is incorrect, and the transaction is canceled.

This trigger prints one message if the insert is rolled back and another if it is accepted. To test for the first condition, try this insert statement:

insert salesdetail 
values ("7066", "234517", "TC9999", 70, 45)

To test for the second condition, enter:

insert salesdetail 
values ("7896", "234518", "TC3218", 75, 80)