Microsoft® SQL Server 2012 Unleashed

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:

Click here to view code image

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:

Click here to view code image

/* transaction inserts rows ...