SQL Server trigger insert update delete example

· If only the inserted table has data, the current operation is insert;
· When both the inserted and deleted tables have data, the current operation is update;
· The current operation is delete only when the deleted table has data.

CREATE TRIGGER

The code is as follows:	Copy code
Create trigger name ON table name For insert, UPDATE, or DELETE AS

Nserted, deleted
This is two virtual tables. inserted stores the tables that are affected by the insert or update operations. deleted stores the tables that are affected by the delete or update operations. Example:
 

The code is as follows:	Copy code
Create trigger tbl_delete On tbl For delete As Declare @ title varchar (200) Select @ title = title from deleted Insert into Logs (logContent) values ('Records with title: '+ title +' deleted ')

Note: If the field value of text or image is obtained from the inserted or deleted virtual table, the obtained value is null.
 
Note: The Trigger name is not enclosed in quotation marks.

The following is an example of online books. When you change the record on the titles table, an email notification is sent to MaryM.

The code is as follows:	Copy code
Create trigger reminder ON titles For insert, UPDATE, DELETE AS EXEC master .. xp_sendmail 'marym ', 'Don't forget to print a report for the distributors .'

# Create an update trigger

The code is as follows:

Copy code

-- 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 saves the pre-update data in the deleted 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;

SQL Server trigger determines the current operation type (insert/update/delete) example

The code is as follows:	Copy code
Create trigger updateRowVersion on PPM_JobBill After insert, update, delete As Begin Declare @ inserted int, @ deleted int Select @ inserted = COUNT (*) from inserted Select @ deleted = COUNT (*) from deleted If @ inserted> 0 and @ deleted = 0 Begin -- Insert End Else if @ inserted> 0 and @ deleted> 0 Begin -- Update End Else if @ inserted = 0 and @ deleted> 0 Begin -- Delete End End Go

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.