SQL Server trigger triggers

The content is excerpt from http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html, only for the use of their own notes

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

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)

The trigger has two special tables: Insert Table (instered table) and Delete table (deleted table). Both of these are logical tables and virtual tables. Two tables 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.

Create a Trigger

Grammar

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 trigger triggers