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 a different one 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)