14 14th Chapter Trigger
DML--after/for UPDATE, INSERT, delete--for cascade delete--INSTEAD of triggers before the time, equivalent to BEFINSERTED, DELETED two sheets to make good use of. when you create a DML trigger, you cannot use the following statements:
Create/alter/drop DATABASELOAD database/load log/reconfigureRESTORE Database/restore LOG
in automatic transaction mode, or in implicit or explicit transaction mode, whenever a BEGIN TRANSACTION statement is emitted in a trigger, a nested transaction is actually started, and when the nested transaction is rolled back using the ROLLBACK TRANSACTION statement in the trigger, The trigger itself emits all the begin TRANSACTION statements when the nested transaction is rolled back, the amount of the trigger itself emits all the begin TRANSACTION statements soy milk is ignored, and ROLLBACK rolls back to the most externally begin TRANSACTION. And before this most external transaction has been committed, it will not be affected .This rollback operation also terminates execution of the statement following the statement in the batch. Therefore, to perform a partial rollback in a trigger, you should use the Save TRANSACTION statement to set a transaction savepoint so that it does not roll back to the external transaction. Note: The statement that writes COMMIT TRANSACTION in the trigger, if preceded by a BEGIN TRANSACTION statement, is considered to commit only the nested transaction, if there is still ROLLBACK TRANSACTION after the commit, then still The most external transaction is rolled back.
if (update (name))To determine which column is updated,columns_updated ()Tests multiple columns, but the column is counted in bytes, and this function returns one or more bytes sorted from left to right. P346 is a very classic application.
specify first trigger and last triggerthe execution between the first and last triggers is not sequential:
Sp_settriggerorder @triggername = ' Ud_trig/ins_trig/del_trig ', @order = ' first/last ', @stmttyp = ' Update/insert/delete ' ;
Since the INSTEAD of trigger has been fired before the base table is updated, it cannot be said that INSTEAD of triggers are specified as the first or last trigger if the ALTER TRIGGER statement is used to change the primary or final trigger, then their order values are removed , you must use Sp_settriggerorder to reset it. Can be execisfirstdeletetrigger by OBJECTPROPERTY () function, execisfirstinserttrigger,execisfirstupdate .... property to determine whether the trigger is a first trigger or the last trigger.
nested and recursive triggers
Whether it is a DML trigger or a DDL trigger, if a trigger occurs, the operation that initiates another trigger is a nested trigger. Layer 32 can be nested through the nested triggers server configuration option to see if the after trigger is available. INSTEAD of trigger nesting is not affected by this option. Refer to the following statement: sp_configure ' nested triggers ', 1--set to 1 to allow after trigger nesting goreconfigure; --Using the new environment value exec sp_configure ' nested triggers '; --View nested triggers option settingsGOrecursive P349 has a classic example because of the update () function detection as a recursive termination condition. Recursion is divided into direct recursion, such as Application update T3 table, triggering trigger TRIG3, Trig3 Update table T3 again, so the trigger Trig3 indirect recursion again. That is, in the middle of another table in transit or trigger the first table: the application updated the table T1, triggering the trigger TRIG1, Trig1 updated table T2, so that the trigger TRIG2.TRIG2 to update the table T1, thus triggering Trig1 again.Note: The After trigger is only allowed to be called recursively if the Recursive_triggers database option is set to on:ALTER DATABASE AdventureWorksSET recursive_triggers on;
Instead of triggers:--instead of insertcreate TRIGGER bef_ins on dbo.test11 instead of Insertasif (exists (SELECT * FROM dbo. Test11 WHERE name = (select name from inserted)) print ' exists already!!! ' Elseinsert to Dbo.test11select Name,gender from inserted
--Instead of update similarly
Instead of both inserts and update must specify values for columns that cannot be empty, but these values need to be ignored in triggers.
3. Using TEXT in instead OF triggers, NTEXT and IMAGE data data modifications may involve text/ntext/image columns. In the base table, the value stored in the Text/ntext or image column is a text pointer, and it only wants to save the page of the data P353
IF EXISTS (SELECT * from sys.triggers WHERE object_id = object_id (N ' [dbo].[ Ins_test] ') DROP TRIGGER [dbo]. [Ins_test] GO
CREATE TRIGGER Ins_stuON dbo. Studentsafter Insertasselect * from INSERTED
INSERT INTO dbo. Studentsvalues (4, ' Frank ', 88.88)
CREATE TRIGGER del_stu on dbo. Studentsfor Deleteasselect * from DELETED
DELETE from dbo. Students WHERE studentname = ' Frank '
CREATE TRIGGER update_stu on dbo. Studentsfor Updateasselect * from Insertedselect * from DELETED
INSERT INTO dbo. Studentsvalues (4, ' Frank ', 88.88) update dbo. Students set classid=5 where studentname= ' Frank ' DELETE from dbo. Students WHERE studentname = ' Frank '
-------------DDL triggers----------------------------DDL triggers are fires that define language statements for the corresponding one or more specific data. And DDL triggers can only run after the SQL statement is complete and cannot be used as INSTEAD of triggers. The Sys.server_triggers catalog view queries server-scoped DDL triggers for information. P354
Modify, delete, and disable triggers drop TRIGGER mytrigger
ALTER TRIGGER Mytriggeron pritableafter Delete as delete from detailtable WHERE OrderID in (SELECT OrderID from Dele Ted);--Disable the trigger method one disable trigger dbo. Pritrigger on dbo. pritable; --DDL triggers cannot contain schema names--Disable Trigger method two
ALTER TABLE dbo. Pritable DISABLE TRIGGER pritrigger;--re-enable trigger method one: Enable TRIGGER dbo. Pritrigger on dbo. pritable--enable trigger: ALTER TABLE dbo. Pritable ENABLE TRIGGER Pritrigger;
Note To delete a DDL trigger, you need to make a scope range for the trigger, otherwise the DML trigger will be deleted by default. DROP TRIGGER Mytriggeron DATABASE; GO
DROP TRIGGER Myothertriggeron all server;
Disabling enable also specifies scope scope: DISABLE TRIGGER Safetyon DATABASE;
ENABLE TRIGGER Safety on DATABASE; To modify a DDL trigger, you should also use the ALTER TRIGGER statement. For example:
ALTER TRIGGER Safetyon DATABASE for create_tableas PRINT N ' CREATE TABLE error '; SELECT EVENTDATA (). VALUE (' (/event_instance/tsqlcommand/commandtext) [1] ', ' nvarchar (max) '); ROLLBACK;
Original link This article by Bean John Blog Backup expert remote One click release
14 14th Chapter Trigger (reprint)
