SQL Server triggers

A trigger is a special type of stored procedure .

The difference between a trigger and a stored procedure is that the trigger is executed automatically by an event trigger, and stored procedures can be called through the name of the stored procedure.

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

Syntax

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 ') isnotnull)
Droptrigger 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 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 ') isnotnull)
Droptrigger Tgr_classes_delete
Go
Create Trigger Tgr_classes_delete
On classes
Fordelete--Delete trigger
As
print ' backup data ... ';    
if (object_id (' classesbackup ', ' U ') isnotnull)
   --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 ') isnotnull)
Droptrigger tgr_classes_update
Go
Create Trigger Tgr_classes_update
On classes
Forupdate
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 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 ') isnotnull)
Droptrigger Tgr_classes_update_column
Go
Create Trigger Tgr_classes_update_column
On classes
Forupdate
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);
Rollbacktran;
End
Go
--Test
SELECT * from student 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 ofupdate ...
As
    T-SQL

# Create instead OF triggers

if (object_id (' tgr_classes_inteadof ', ' TR ') isnotnull)
Drop Trigger Tgr_classes_inteadof
Go
Create Trigger Tgr_classes_inteadof
On classes
    Instead ofdelete/*, 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 ID;
SELECT * from Classes;
Delete classes where id = 7;

# Show custom Message RAISERROR

if (object_id (' tgr_message ', ' TR ') isnotnull)
Drop Trigger Tgr_message
Go
Create Trigger Tgr_message
On student
After insert, update
Asraiserror (' 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 ID;

# Modify Trigger

Alter TRIGGER Tgr_message
On student
After delete
Asraiserror (' Tgr_message trigger is triggered ', 16, 10);
Go
--test
Deletefrom 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 ') isnotnull))
Droptrigger Tgr_valid_data
Go
Createtrigger 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);
Rollbacktran;
End
Go
--test
INSERT into student values (' Forest ', 2, 0, 7);
INSERT into student values (' Forest ', 22, 0, 7);
SELECT * from student ID;

# example, action log

if (object_id (' Log ', ' U ') isnotnull)
Droptable Log
Go
CreateTable log (
ID intidentity (1, 1) PrimaryKey,
Actionvarchar (20),
CreateDate datetime default GetDate ()
)
Go
if (exists (SELECT * from sys.objects where name = ' Tgr_student_log '))
Droptrigger Tgr_student_log
Go
Createtrigger 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
ElseIf (Exists (select 1 from inserted) andnotexists (select 1 from deleted))
Begin
Insert into log values (' inserted ');
End
ElseIf (notexists (select 1 from inserted) andexists (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 ID;

SQL Server triggers