لطفا منتظر باشید ...
Trigger Restrictions
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.
