SQL SERVER triggers introduction _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.

Action on a table	Inserted logical table	Deleted logical Table
Add record (insert)	Store Added Records	No
Delete Record (delete)	No	Store deleted records
Modify record (update)	Store Updated Records	Record before the update

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 insert Insert Type Trigger if (object_id (' Tgr_classes_insert ', ' tr ') is not null) DROP trigger Tgr_classes_insert go create TRIGGER Tgr_classes
_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 Go 
Create trigger Tgr_classes_delete on classes for delete--delete triggers as print ' backup data ... '; if (object_id (' classesbackup ', ' U ') is not null)--there is a classesbackup, insert data directly into ClasSesbackup select name, 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 Go
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--The updated data Select @newName = name from Inserte
D
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 the order by ID;
SELECT * from Classes; 
Update classes Set name = ' Five class ' WHERE name = ' Class 5 '; The update trigger updates the data before it is updatedData is saved in the deleted table, 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 C Reate trigger Tgr_classes_update_column on classes for update as-column-level triggers: Update class creation time if (update (createdate)) Begin Raiserr or (' 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 record with update;

# instead of type triggers

Instead OF triggers represent actions that do not perform their definition (INSERT, UPDATE, delete) and only the contents of the trigger itself.

Create syntax

CREATE trigger Tgr_name on table_name with encryption instead of update ... as T-SQL # creates instead OF triggers if (object_id (' T Gr_classes_inteadof ', ' TR ') are NOT null) drop trigger tgr_classes_inteadof go to create trigger Tgr_classes_inteadof on class
Es 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_message
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 wherename = ' 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--disable 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, a ction varchar, createdate datetime default GetDate ()) go to if (exists (SELECT * from sys.objects where name = ' Tgr_stud Ent_log ')) drop trigger Tgr_student_log go create trigger Tgr_student_log in student after insert, UPDATE, delete 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 from 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 to insert 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;