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