Trigger Restrictions

for RuBoard

There are a handful of restrictions that apply to triggers. First and foremost, there are a number of commands that are invalid within triggers because triggers run within (at least) implicit transactions, and these commands are not allowed within transactions:

  • ALTER DATABASE

  • CREATE DATABASE

  • DISK INIT

  • DISK RESIZE

  • DROP DATABASE

  • LOAD DATABASE

  • LOAD LOG

  • RECONFIGURE

  • RESTORE DATABASE

  • RESTORE LOG

  • UPDATE STATISTICS

Because a number of commands require that they be the first statement in the current batch, running them from within a stored procedure or trigger requires special treatment. Examples of statements that must be the first statement in a batch include CREATE VIEW, CREATE PROCEDURE, and many others. Here's a trigger that creates a view and then inserts a row using that view (Listing 8-7):

Listing 8-7 Like stored procedures, triggers can execute commands you might otherwise think were not allowed.
 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int,  Onhand int ) CREATE TABLE ToyAudit (ToyAudit int identity,  Operation varchar(10),  Toy int,  Type int,  Change int ) GO CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT AS IF @@ROWCOUNT=0 RETURN EXEC('IF OBJECT_ID(''TA'') IS NOT NULL DROP VIEW TA') EXEC('CREATE VIEW TA AS SELECT * FROM ToyAudit') INSERT TA SELECT 'INSERT', * FROM inserted GO INSERT ToyInventory DEFAULT VALUES 

Because CREATE VIEW must be the first statement in a batch, we use EXEC to run it. This places it in its own batch. We also execute its DROP separately so that, again, the CREATE remains the first statement in its batch.

With the exception of CREATE TABLE, most object creation commands must be handled this way within a trigger. The upside is that you can use information from the current execution environment to build the CREATE dynamically.

There are also restrictions regarding INSTEAD OF triggers and cascading referential integrity. An INSTEAD OF trigger cannot be defined for an UPDATE operation on a table that has a cascading UPDATE constraint defined. The same is true for INSTEAD OF DELETE triggersyou can't create one on a table with a cascading DELETE constraint defined.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net