15. SQL Server triggers

SQL Server triggers

A trigger is a special stored procedure that is triggered only when you attempt to modify data using the data manipulation language DML, which contains the add, delete, and change of the view and table.

Triggers are divided into DML triggers and DDL triggers, where DML triggers are triggered when the data table, the view, is insert, UPDATE, delete. DDL is triggered when create, alter, drop, and so on.

DML triggers are also divided into after (then triggered) and insert of (previously triggered) triggers.

If you do not specify a type when creating a trigger, the by default is an after trigger.

The trigger has two virtual temporary tables: the Insert Table (inserted) and the delete table (deleted), which are created in memory by the system and are not saved to the database. And both tables are read-only. When the trigger finishes working, the two tables are automatically deleted.

Added: Inserted table record

Delete: Deleted table record

Modified: Inserted table records modified before deleted table records modified

When you modify the data, delete the record, and then add a new day to the record. So inserted and updated are all recorded.

The trigger itself is a transaction that can take advantage of the rollback of the transaction and undo the operation. A transaction is either executed, or none is executed.

Create a Trigger

Grammar:

Create Trigger [Shema_name.]Trg_name on{Table | View }[With encryption]{  for |After|instead of }{ Insert,Update,Delete } assql_statement

Create an INSERT Trigger

if(object_id('Tgr_test_insert','TR') is  not NULL)    Drop TriggerTgr_test_insert--Delete TriggerGoCreate TriggerTgr_test_insert onStudent--Create a table in student     for Insert --Insert Trigger asDeclare @id int,@name varchar( -),@sex Char(2),@age int--querying information that has been inserted in the inserted tableSelect @id =Id@name =Name@sex =Sex@age =Age frominserted--Add the information you've found to the membership tableif(@age > -)--Modify if the age is not metbegin    UpdateStudentSetAge=  - whereId= @id    Print 'too old to automatically change to'EndInsert  into [User](Username,password,roleid)Values(@name,@sex + cast(@age  as varchar),3)Print 'Add Student Success! ';Print 'Add member Success! '

After you create a trigger on the student table after you insert the

Insert  into Values (' pirate captain ',' male ',' n ','[email protected ]')

View Run Results

Create an UPDATE trigger

if(object_id('tgr_student_update','TR') is  not NULL)    Drop Triggertgr_student_updateGoCreate Triggertgr_student_update onStudent for Update as    Declare @oldName varchar(Ten),@newName varchar(Ten)    Select @oldName =Name fromDeleted--find pre-update data    Select @newName =Name fromInserted--Find updated Data    if(@oldName = @newName)    begin        Print 'Same Data'        rollback Tran;--Rollback does not perform modify operations    End    Else        Print 'Modification succeeded'

Update Set name=' pirate captain '

The transaction ends in the trigger. The batch has been aborted.

Update Set name=' pink empress '

Modification succeeded

Create a Delete trigger

if(object_id('Tgr_student_delete','TR') is  not NULL)    Drop TriggerTgr_student_deleteGoCreate TriggerTgr_student_delete onStudent for Delete as    if(exists(Select *  fromsys.databaseswhereName= 'Studentbackup'))        Insert  intoStudentbackupSelectName,sex,age,mail fromdeleted; Else        Print 'not present, create and reinsert'        Create TableStudentbackup (Namevarchar( -), SexChar(2), ageint, Mailvarchar( -)        )        Insert  intoStudentbackupSelectName,sex,age,mail fromDeleted

Delete  from Student Select *  from Student Select *  from Studentbackup

Create instead OF triggers

if(object_id('tgr_student_inteadof','TR') is  not NULL)    Drop Triggertgr_student_inteadofGoCreate Triggertgr_student_inteadof onstudent instead of Delete --Update, insert as    Declare @id int,@name varchar( -)    Select @id =Id@name =Name fromdeleted; --instead of first trigger    --Delete User table information first    Delete  from [User] whereUserName= @name; --Delete Student table information again    Delete  from [Student] whereId= @id;Delete  fromStudentwhereName= 'Pirate Captain'

modifying triggers

Alter Trigger Tgr_test_insert  on Student  for Insert  as Print ' OK '

disabling triggers

Trigger  on Student

enabling triggers

Trigger  on Student

Query a trigger that has been created

Select *  from Sys.triggers

15. SQL Server triggers