An in-depth analysis of SQL Server triggers _mssql

A trigger is a special type of stored procedure that differs from the stored procedure we described earlier. Triggers are triggered primarily by events that are automatically invoked to execute. Stored procedures can be invoked by the name of the stored procedure.

Ø what is a trigger

A special stored procedure that is automatically executed when a trigger inserts, updates, and deletes a table. Triggers are generally used for more complex constraints on check constraints. The difference between a trigger and a normal stored procedure is that a trigger is an operation on a table. such as: UPDATE, INSERT, delete these operations, the system will automatically invoke the execution of the corresponding trigger on the table. Triggers in SQL Server 2005 can be grouped into two categories: DML triggers and DDL triggers, where DDL triggers affect multiple data definition language statements that have create, alter, and DROP statements.

DML triggers are divided into:

1, after trigger (after trigger)
A, insert trigger
B, update triggers
C, delete trigger
2, instead OF triggers (previously triggered)

The AFTER triggers require that triggers be triggered only after an action has been taken to insert, UPDATE, delete, and can only be defined on the table. The instead of triggers represent actions that do not perform their definition (INSERT, UPDATE, DELETE) and only execute the trigger itself. You can define instead OF triggers on a table or on a view.

Triggers have two special tables: Insert Table (instered table) and delete table (deleted table). These are both logical tables and virtual tables. There are two tables in memory created by the system that are not stored in the database. Both tables are read-only and can only read data and not modify the data. The results of these two tables are always the same as the structure of the tables that are applied by the modified triggers. When the trigger finishes working, the two tables are deleted. Inserted table data is inserted or modified data, and deleted table data is updated or deleted.

Update data is to delete the table record first, then add a record. This will have updated data records in both the inserted and deleted tables. Note that the trigger itself is a transaction, so it is possible to make some special checks on the modified data in the trigger. If you are not satisfied you can take advantage of transaction rollback, undo operation.

Ø Create triggers

Grammar

CREATE trigger Tgr_name on table_name with encrypion– cryptographic triggers for UPDATE ... as Transact-SQL # Create INSERT type trigger--Create Inser T insert type Trigger if (object_id (' Tgr_classes_insert ', ' tr ') is not null) DROP trigger Tgr_classes_insert go create TRIGGER TGR_CL
  Asses_insert on classes for insert--insert trigger as--Define variable declare @id int, @name varchar, @temp int;
  --In the inserted table query has inserted record information select @id = id, @name = name from inserted;
  Set @name = @name + convert (varchar, @id);  
  Set @temp = @id/2;
  INSERT into student values (@name, + @id, @temp, @id); print ' Add Student success!
';
Go-insert data into classes values (' Class 5 ', GetDate ());
--Query data select * from classes;
   SELECT * FROM student the order by ID;
  Insert Trigger, you add a record that you just inserted in the inserted table. # Create a Delete type trigger--delete Delete type Trigger if (object_id (' Tgr_classes_delete ', ' TR ') is not null) drop trigger Tgr_classes_delete g  
  o CREATE trigger Tgr_classes_delete on classes for delete--delete triggers as print ' backup data ... ' if (object_id (' classesbackup ', ' U ') is not null)--SaveIn Classesbackup, insert data directly into Classesbackup select name and createdate from deleted;
  Else--there is no classesbackup creation and insert select * into Classesbackup from deleted; print ' Backup data successful!
';
The go----does not show the number of rows--set nocount on;
Delete classes WHERE name = ' 5 class ';
--Query data select * from classes;
  SELECT * from Classesbackup;
  The delete trigger saves the data that you just deleted in the deleted table when you delete it. # Create UPDATE type triggers--update update type triggers if (object_id (' tgr_classes_update ', ' TR ') is not null) drop trigger Tgr_classes_update g
  o CREATE trigger tgr_classes_update on classes for update as declare @oldName varchar (a), @newName varchar (20);
  --Pre-update data Select @oldName = name from deleted; if (exists (SELECT * from student where name like '% ' + @oldName + '% ')) Begin--Updated data Select @newName = n
      Ame from inserted;
      Update student Set name = replace (name, @oldName, @newName) where name like '% ' + @oldName + '% '; print ' cascade Modify data Success!
    '; End else print ' does not need to modify the student table!
'; Go--Query data select * FROM Student OrdeR by ID;
SELECT * from Classes;
   Update classes Set name = ' Five class ' WHERE name = ' Class 5 ';
  The update trigger saves the updated data in the deleted table after the data is updated, and the updated data is saved in the inserted table.
# Update column-level triggers if (object_id (' Tgr_classes_update_column ', ' TR ') are NOT null) drop trigger Tgr_classes_update_column Go Create trigger Tgr_classes_update_column on classes for update as-column-level triggers: Update class creation time if (update (createdate)) Begi n raisError (' System Hint: class creation time cannot be modified!
    ', 16, 11);
  Rollback Tran;
End Go--Test select * FROM student the order by ID;
SELECT * from Classes;
Update classes Set createdate = GetDate () where id = 3;
   Update classes Set name = ' Four classes ' where id = 7;
    Update column-level triggers can update the column records with update or not; # instead of type triggers instead OF triggers represent actions that do not perform their definition (INSERT, UPDATE, delete) and only the content that executes the trigger itself. Creating syntax create trigger TGR_NAME on table_name with encryption instead of update ... as T-SQL # Create instead OF triggers if (obje ct_id (' tgr_classes_inteadof ', ' TR ') is not null) DROP trigger tgr_classes_inteadof go create TRIGGER Tgr_classes_inteado F on classEs instead of delete/*, UPDATE, insert*/as declare @id int, @name varchar (20);
  --Query for deleted information, disease assignment Select @id = id, @name = name from deleted;
  print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name;
  --First Delete student information Delete student WHERE cid = @id;
  --then delete the classes information Delete classes where id = @id; print ' Delete [ID: ' + convert (varchar, @id) + ', Name: ' + @name + '] Information success!
';
Go--test select * FROM student the order by ID;
SELECT * from Classes;
   Delete classes where id = 7; # Display Custom Message RAISERROR if (object_id (' tgr_message ', ' TR ') is not null) DROP trigger tgr_message go create TRIGGER Tgr_messag
E on student after insert, update as RaisError (' tgr_message triggers are triggered ', 16, 10);
Go--test inserts into student values (' Lily ', 22, 1, 7);
Update student Set sex = 0 where name = ' Lucy ';
  SELECT * FROM student the order by ID;
# modify triggers Alter TRIGGER tgr_message on student after delete as raisError (' tgr_message triggers are triggered ', 16, 10);
  Go--test deletes from student where name = ' Lucy '; # Enable, disable triggers--forbiddenUsing triggers disable trigger tgr_message on student;
  --Enables trigger enable trigger tgr_message on student;
# query-created trigger information-query for existing triggers select * from Sys.triggers;
SELECT * from sys.objects where type = ' TR '; --View Trigger trigger event Select te.* from sys.trigger_events te join sys.triggers t on t.object_id = te.object_id where T.parent_class
= 0 and T.name = ' tgr_valid_data ';
  --View CREATE TRIGGER statement exec sp_helptext ' tgr_message '; # example, validating the Insert data if ((object_id (' Tgr_valid_data ', ' TR ') is not null) DROP trigger tgr_valid_data go create TRIGGER Tgr_valid
  _data on student Insert as declare @age int, @name varchar (20);
  Select @name = s.name, @age = s.age from inserted s;
    if (@age <) begin RaisError ("Age with new data inserted problem", 16, 1);
  Rollback Tran;
End Go--test inserts into student values (' Forest ', 2, 0, 7);
INSERT into student values (' Forest ', 22, 0, 7);
  SELECT * FROM student the order by ID; # example, action log if (object_id (' Log ', ' U ') is isn't null) drop table log go CREATE TABLE log (id int identity (1, 1) PrimarY key, Action varchar, createdate datetime default GetDate ()) go to if (exists select * from sys.objects where name = ' tgr_student_log ') drop trigger tgr_student_log go create trigger Tgr_student_log on student after insert, update, D  Elete as if ((Exists (select 1 from inserted)) and (exists (select 1 from deleted)) begin inserts into log (action)
  VALUES (' updated ');  End else if (exists (select 1-inserted) and not exists (select 1 from deleted), begin insert into log (action)
  VALUES (' inserted ');  End else if (not exists (select 1 from inserted) and exists (select 1 from deleted) begin inserts into log (action)
  VALUES (' deleted ');
End Go--test inserts into student values (' King ', 22, 1, 7);
Update student Set sex = 0 where name = ' King ';
Delete student where name = ' King ';
select * from log; SELECT * FROM student the order by ID;

Above is this article to you in-depth analysis of SQL Server triggers all the content, I hope you like.