Use of SQL Server triggers

Use of SQL Server triggers

 

 

There are two tables, A and B. Records in Table B are taken from Table.
A:
ID (PK), name, sex, job, address ,....
B:
ID (PK), name, address

Now you want to create a trigger on A. When the record in a is increased, a field (Address) is updated, and the record is deleted, add, update, and delete records in B.

 

We can both write Three triggers and one trigger. However, to write a trigger, you need to determine the type of the current operation, so there are no three efficiency points.

 

 

1. Trigger

 

-- Add Synchronization
Create trigger tr_insert_a on
For insert
As
Insert B (ID, name, address) Select ID, name, address from inserted
Go

-- Delete Synchronization
Create trigger tr_delete_a on
For Delete
As
Delete B from deleted d Where B. ID = D. id
Go

-- Update Synchronization
Create trigger tr_update_a on
For update
As
If Update (name) or update (address)
Update B set name = I. Name, address = I. Address
From B, inserted I, deleted d
Where I. ID = D. id and I. ID = B. ID

 

 

2. Write a trigger

Create trigger tr_insert_update_delete_a on
For insert, update, delete
As
If not exists (select * From deleted) -- if it is new

Insert B (ID, name, address) Select ID, name, address from inserted

 

Else if not exists (select * From inserted) -- delete

Delete B from deleted d Where B. ID = D. id

Else if Update (name) or update (Address) -- if it is an update

 
Update B set name = I. Name, address = I. Address
From B, inserted I, deleted d
Where I. ID = D. id and I. ID = B. ID

 

 

Note:

 

1. Insert)

The inserted Table has data, and the deleted table has no data.

2. Delete)

The inserted Table has no data, and the deleted table has data.

3. Update)

The inserted Table has data (new data), and the deleted table has data (old data)

 

In addition:

What should we do if there are two such tables? The key field of primary A has changed
There are two tables, A and B. Records in Table B are taken from Table.
A:
Name (PK), sex, job, address ,....
B:
ID (PK), name, address

SO 2 can be changed

Create trigger tr_insert_update_delete_a on
For insert, update, delete
As
If not exists (select * From deleted) -- if it is new

 
Insert B (name, address) Select name, address from inserted

Else if not exists (select * From inserted) -- delete

Delete B from deleted d Where B. Name = D. Name

 
Else if Update (name) or update (Address) -- if it is an update

Update B set name = I. Name, address = I. Address
From B, inserted I, deleted d
Where I. Name = D. Name and I. Name = B. Name

 

The insert and delete triggers are correct, but the following issues occur during update: when the key field name of Table A is updated, the name field of Table B cannot be updated !!

 

Changed to this:

 

Create trigger tr_insert_update_delete_a on
For insert, update, delete
As

Declare @ r int
Set @ r = @ rowcount
If @ r = 0 return

If not exists (select * From deleted) -- if it is new

Insert B (name, address) Select name, address from inserted

Else if not exists (select * From inserted) -- delete

Delete B from deleted d Where B. Name = D. Name

 
Else if Update (name) and @ r> 1 -- If name is updated, many rows are not allowed; otherwise, synchronization cannot be implemented.

Begin
Raiserror ('the primary key value of multiple records cannot be updated at the same time. The update is canceled! ', 1, 16)
Rollback tran
Return
End
Else if Update (name)
Update B set name = I. Name, address = I. Address
From B, inserted I, deleted d
Where B. Name = D. Name
Else if Update (address)
Update B set name = I. Name, address = I. Address
From B, inserted I, deleted d
Where B. Name = D. Name and I. Name = D. Name

 

 

It should be noted that if the trigger code is too complex or inefficient, because the trigger action is often to update data, a certain amount of exclusive locks will be generated, for triggers, efficiency must always be paid attention!

In addition, tables with triggers in batch may be slow. However, for a single piece of data, the impact is very small. Because these are all executed in the memory.