SQL SERVER Triggers introduction

What is a trigger
A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are typically used on more complex constraints of check constraints. The difference between a trigger and a normal stored procedure is that the trigger is when you manipulate 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 fall into two categories: DML triggers and DDL triggers, where DDL triggers are fired that affect a variety of data definition language statements that have create, alter, and DROP statements.
DML triggers are divided into:
1. After trigger (trigger later)
A, insert trigger
B, UPDATE trigger
C, delete trigger
2. Instead of trigger (previously triggered)
Where after triggers require that triggers be triggered only after an operation is executed, INSERT, UPDATE, delete, and can only be defined on the table. The instead of trigger indicates that the action (INSERT, UPDATE, DELETE) does not execute its definition, but only the execution of the trigger itself. You can define instead OF triggers on a table or on a view.
The trigger has two special tables: Insert Table (instered table) and delete table (deleted table). Both of these are logical tables and virtual tables. There are two tables that are created by the system in memory and are not stored in the database. Both tables are read-only and can only read data and not modify the data. The result of these two tables is always the same as the structure of the table to which the trigger was modified. When the trigger finishes working, the two tables are deleted. The data of the inserted table is inserted or modified, and the data of the deleted table is the data before or after the update.
Operations on a table
Inserted logic table
Deleted logic Table
Add record (insert)
Storage of Added Records
No
Delete a record (delete)
No
To store deleted records
Modify record (update)
Storage of updated records
Store pre-update records
When you update the data, you delete the table records and then add a record. This allows the inserted and deleted tables to have updated data records. Note that the trigger itself is a transaction, so you can make some special checks on the modified data in the trigger. If the transaction can be rolled back, the undo operation is not satisfied.
? Create a Trigger
Grammar
Create Trigger Tgr_name
On table_name
With encrypion– encryption trigger
? 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
? --Defining variables
? declare @id int, @name varchar (), @temp int;
? --In the inserted table, the 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
--Inserting data
Insert into classes values (' Class 5 ', GetDate ());
--Querying data
SELECT * from Classes;
SELECT * FROM student the order by ID;
Insert trigger, a newly inserted record is added to 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 trigger
As
? print ' backup data ... ';
? if (object_id (' classesbackup ', ' U ') is not null)
??? --presence of classesbackup, direct insertion of data
??? INSERT into Classesbackup select name, createdate from deleted;
? Else
??? --There is no classesbackup create and reinsert
??? SELECT * into Classesbackup from deleted;
? print ' backup data is successful! ‘;
Go
--
--does not show the number of affected rows
--set nocount on;
Delete classes WHERE name = ' 5 class ';
--Querying data
SELECT * from Classes;
SELECT * from Classesbackup;
The delete trigger saves the data that you just deleted in the deleted table when you delete the data.
# Create an update type trigger
--update Update Type Trigger
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 (), @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 = name from inserted;
????? Update student Set name = replace (name, @oldName, @newName) where name like '% ' + @oldName + '% ';
????? PRINT ' Cascade modified data successfully! ‘;
??? End
? Else
??? print ' No need to modify the student table! ‘;
Go
--Querying data
SELECT * FROM student the order by ID;
SELECT * from Classes;
Update classes Set name = ' Five class ' WHERE name = ' 5 class ';
The update trigger will save the updated data in the deleted table after updating the data, and the updated data will be saved in the inserted table.
# Update Updates column-level triggers
if (object_id (' Tgr_classes_update_column ', ' TR ') is not null)
? Drop Trigger Tgr_classes_update_column
Go
Create Trigger Tgr_classes_update_column
On classes
? For update
As
? --Column-level triggers: Whether class creation time has been updated
? if (update (createdate))
? Begin
??? RaisError (' System Tip: 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 = ' Class Four ' where id = 7;
Update column level triggers can update column records with update;
# instead of type triggers
Instead of triggers indicate that the operation (INSERT, UPDATE, delete) is not executed, but only the contents of the trigger itself.
Create syntax
Create Trigger Tgr_name
On table_name
With encryption
? Instead of update ...
As
? T-SQL
# Create instead OF triggers
if (object_id (' tgr_classes_inteadof ', ' TR ') is not null)
? Drop Trigger Tgr_classes_inteadof
Go
Create Trigger Tgr_classes_inteadof
On classes
? Instead of delete/*, update, insert*/
As
? declare @id int, @name varchar (20);
? --Query for deleted information, sickness assignment value
? Select @id = id, @name = name from deleted;
? print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name;
? --Delete student's information first
? Delete student WHERE cid = @id;
? --Delete the classes information again
? Delete classes where id = @id;
? print ' Delete [ID: ' + convert ' (varchar, @id) + ', Name: ' + @name + '] Information succeeded! ‘;
Go
--test
SELECT * FROM student the order by ID;
SELECT * from Classes;
Delete classes where id = 7;
# Show 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 trigger is triggered ', 16, 10);
Go
--test
INSERT into student values (' Lily ', 22, 1, 7);
Update student Set sex = 0 where name = ' Lucy ';
SELECT * FROM student the order by ID;
# Modify Trigger
Alter TRIGGER Tgr_message
On student
After delete
As RaisError (' Tgr_message trigger is triggered ', 16, 10);
Go
--test
Delete from student where name = ' Lucy ';
# Enable, disable triggers
--Disabling triggers
Disable trigger tgr_message on student;
--Enable Trigger
Enable trigger tgr_message on student;
# Query for trigger information created
--Query for triggers that already exist
SELECT * from Sys.triggers;
SELECT * from sys.objects where type = ' TR ';

--View Trigger trigger events
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 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
After insert
As
? DECLARE @age int,
????? @name varchar (20);
? Select @name = s.name, @age = s.age from inserted s;
? if (@age < 18)
? Begin
??? RaisError (' Age of inserting new data ' is problematic ', 16, 1);
??? Rollback Tran;
? End
Go
--test
INSERT 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 not null)
drop TABLE Log
Go
CREATE TABLE log (
ID int Identity (1, 1) primary key,
Action varchar (20),
CreateDate datetime default GetDate ()
)
Go
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, delete
As
if ((Exists (select 1 from inserted) and (exists (select 1 from deleted))
Begin
Insert into log values (' updated ');
End
else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
Begin
Insert into log values (' inserted ');
End
else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
Begin
Insert into log values (' deleted ');
End
Go
--test
INSERT 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;

SQL SERVER Triggers introduction