Triggers and Multistatement Transactions
Now let’s look at another example. First, you need to create a trigger to enforce referential integrity between the titles
table and publishers
table:
create trigger tr_auditlog_i on auditlog for insert asdeclare @rows int -- create variable to hold @@rowcountselect @rows = @@rowcountif @rows = 0 returnif update(valuentered) and (select count(*) from inserted i, test_table tt where tt.col1 = i.valuentered ) != @rows begin rollback transaction raiserror ('Invalid value inserted', 16, 1) endreturngo
Now, run a multistatement transaction with an invalid value
in the second insert
statement:
/* transaction inserts rows ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.