SQL trigger operation details

A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for constraints with more complex check constraints. The difference between a trigger and a Common Stored procedure is that a trigger is used to operate a table. For example, update, insert, and delete operations, the system automatically calls the trigger for executing the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

There are three common triggers: Insert, Update, and Delete events.

Why should I use a trigger? For example, two tables:

  

Create Table Student (-- Student Table
StudentID int primary key, -- student ID
....
       )

Create Table BorrowRecord (-- student borrow record Table
BorrowRecord int identity (1, 1), -- serial number
StudentID int, -- student ID
BorrowDate datetime, -- lending time
ReturnDAte Datetime, -- return time
...
      )

The following functions are used:
1. If I change the student's student ID, I hope that his borrowing record will still be related to this student (that is, changing the student ID of the borrowing record at the same time );
2. If the student has graduated, I want to delete his student ID and his/her borrowing history.
And so on.

A trigger can be used at this time. For 1, create an Update trigger:

   

Create Trigger truStudent
On Student -- create a trigger in the Student table
For Update -- why events are triggered
As-what to do after an event is triggered
If Update (StudentID)
Begin

Update BorrowRecord
Set StudentID = I. StudentID
From BorrowRecord br, Deleted d, Inserted I -- Deleted and Inserted temporary tables
Where br. StudentID = d. StudentID

End
                

Understand the two temporary tables in the trigger: Deleted and Inserted. Note: Deleted and Inserted indicate the "old record" and "new record" tables that trigger the event respectively ".
In a database tutorial system, two virtual tables are used to store the changes recorded in the table, which are:
Virtual Table Inserted virtual table Deleted

New records are stored when table records are added. New records are not stored.
New record used for update stored during modification
The deleted records are not stored during deletion.

An Update process can be viewed as: generate a new record to the Inserted table, copy the old record to the Deleted table, delete the Student record, and write a new record.

 

For 2, create a Delete trigger
Create trigger trdStudent
On Student
For Delete
As
Delete BorrowRecord
From BorrowRecord br, Delted d
Where br. StudentID = d. StudentID

    

From the two examples, we can see the key of the trigger: A.2 temporary tables; B. Trigger mechanism

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

 

2. instead of trigger (previously triggered)

 

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a View.

 

The trigger has two special tables: the insert table and the delete table ). These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the database. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

 

Table operations	Inserted logical table	Deleted logical table
Insert)	Store added Records	None
Delete)	None	Store deleted records
Update)	Store updated records	Store records before update

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. Note: the trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to cancel the operation.

 

Create a trigger

 

Syntax

Create trigger tgr_name
On table_name
With encrypion-encryption trigger
For update...
As
Transact-SQL

# Create an insert trigger

-- Create an insert 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 variables
Declare @ id int, @ name varchar (20), @ temp int;
-- Query inserted records in the inserted table
Select @ id = id, @ name = name from inserted;
Set @ name = @ name + convert (varchar, @ id );
Set @ temp = @ id/2;
Insert into student values (@ name, 18 + @ id, @ temp, @ id );
Print 'student added successfully! ';
Go
-- Insert data
Insert into classes values ('class 5 ', getDate ());
-- Query data
Select * from classes;
Select * from student order by id;

Insert trigger. A newly inserted record is added to the inserted table.

 

# Create a delete trigger

-- Delete 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 -- trigger deletion
As
Print 'backing up data ...... ';
If (object_id ('classesbackup', 'u') is not null)
-- ClassesBackup exists to insert data directly.
Insert into classesBackup select name, createDate from deleted;
Else
-- ClassesBackup does not exist before creation and insertion
Select * into classesBackup from deleted;
Print 'data backed up successful! ';
Go
--
-- The number of affected rows is not displayed.
-- Set nocount on;
Delete classes where name = 'class 5 ';
-- Query data
Select * from classes;
Select * from classesBackup;

The delete trigger will save the deleted data in the deleted table when deleting the data.

 

# Create an update trigger

-- 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 (20), @ newName varchar (20 );
-- Data before update
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 data modified successfully! ';
End
Else
Print 'no 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 = 'five class ';

After the update trigger updates the data, it stores the pre-update data in the inserted table and the updated data in the inserted table.

 

# Update a column-level trigger

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 trigger: whether the class creation time is updated
If (update (createDate ))
Begin
RaisError ('system prompt: the class creation time cannot be modified! ', 16, 11 );
Rollback tran;
End
Go
-- Test
Select * from student order by id;
Select * from classes;
Update classes set createDate = getDate () where id = 3;
Update classes set name = 'four class' where id = 7;

You can use update to determine whether to update column records;

 

# Instead of type trigger

The instead of trigger indicates that the operation defined by the trigger is not executed (insert, update, delete), but only the content of the trigger itself.

Create syntax

Create trigger tgr_name
On table_name
With encryption
Instead of update...
As
T-SQL

   

# Create An instead of trigger

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 the deleted information and assign values to the disease
Select @ id = id, @ name = name from deleted;
Print 'Id: '+ convert (varchar, @ id) +', name: '+ @ name;
-- Delete student information first
Delete student where cid = @ id;
-- Delete the classes information.
Delete classes where id = @ id;
Print 'the message [id: '+ convert (varchar, @ id) +', name: '+ @ name +'] is deleted successfully! ';
Go
-- Test
Select * from student 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 trigger triggered ', 16, 10 );
Go
-- Test
Insert into student values ('Lily', 22, 1, 7 );
Update student set sex = 0 where name = 'Lucy ';
Select * from student order by id;

# Modify a trigger

Alter trigger tgr_message
On student
After delete
As raisError ('tgr _ message trigger triggered ', 16, 10 );
Go
-- Test
Delete from student where name = 'Lucy ';

# Enable or disable a trigger

-- Disable a trigger
Disable trigger tgr_message on student;
-- Enable trigger
Enable trigger tgr_message on student;

# Querying created trigger information

-- Query existing triggers
Select * from sys. triggers;
Select * from sys. objects where type = 'tr ';

-- View 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 the trigger creation statement
Exec sp_helptext 'tgr _ message ';

 

# Example: verify the inserted 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 new data inserted has a problem ', 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 order by id;

# Example: Operation 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 (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
Insert into log (action) 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 order by id;