O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required