Do you really understand trigger? Analysis of Real-time Data Synchronization and update Problems

When we want to update a dynamic table (that is, the data in the table is constantly added), we may use the database proxy to write homework, then, let him regularly query the latest data in the dynamic table, and then update the data. In this way, you can update the data, but the data cannot be updated synchronously in real time.

At this time, the trigger is what we want. We can create a trigger on that dynamic table. The trigger is actually a stored procedure, but the time it calls is executed based on the created dynamic table that occurs in the table (that is, Insert new data, Update or Delete data ).

I will not introduce how to use the trigger here. There is a lot of information in the garden. So what do I want to introduce today?
A few days ago, when I was writing SQL code, I accidentally found this problem: I always thought that every time a piece of data is inserted in a dynamic table, the trigger will be executed once, but I understand this, when data is inserted in batches, the trigger executes the same number of times as the number of inserted rows, but this is not the case. I want to write and share with you a little time today. If you are not talking about it, please make an axe!

Below is a simple example for your reference.
Copy codeThe Code is as follows:
-- Create a dynamic table for the trigger.
Create table Table_a
(
ID int identity (), -- auto-increment ID
Content nvarchar (50 ),
UpdateIDForTrigger int
)

Then we create a trigger on the table.
Copy codeThe Code is as follows:
Create TRIGGER [dbo]. [Table_a_Ins]
ON [dbo]. [Table_a]
AFTER INSERT
AS
BEGIN
Declare @ ID int
Set @ ID = (select ID from inserted)
-- Update the value of the UpdateIDForTrigger field in Table _ A to show more obvious real-time execution results.
UPDATE Table_a
SET UpdateIDForTrigger = (@ ID + 10) -- in order to see the difference, the value 10 greater than the ID is directly assigned as the variable value.
Where id = @ ID;
END

Next, we will test the insert results based on the general Strips:
Copy codeThe Code is as follows:
-- Add data to the information table
Insert into Table_a (Content) values ('information 1 ');
Insert into Table_a (Content) values ('information 2 ');

Query the data in the current dynamic table
Copy codeThe Code is as follows:
Select * from Table_a

Query Result
 
We can see that the trigger is executed. The trigger simultaneously performs the Update function when each data entry is inserted.
Next, we want to insert data in batches. To facilitate data insertion, we will create a temporary basic information table:
Copy codeThe Code is as follows:
-- Basic info table
Create table Table_Info
(
ID int identity (1, 1 ),
Content nvarchar (50)
)

Insert data
Copy codeThe Code is as follows:
Insert into Table_Info (Content) values ('information 3 ');
Insert into Table_Info (Content) values ('information 4 ');
Insert into Table_Info (Content) values ('information 5 ');
Insert into Table_Info (Content) values ('info 6 ');
Insert into Table_Info (Content) values ('information 7 ');
Insert into Table_Info (Content) values ('information 8 ');
Insert into Table_Info (Content) values ('information 9 ');
Insert into Table_Info (Content) values ('information 10 ');

Then we can insert data in batches to dynamic tables.
Copy codeThe Code is as follows:
Insert into Table_a (Content)
Select Content from Table_Info

This time, the message box prompts an error when executing this SQL statement:
 
Experienced friends will know that this error is caused by the assignment of "=" to a variable in multiple results.
That is: set @ variable = (select multi-row result from Table)
At this time, I am confused. Where is the problem? Isn't the trigger executed once every time a piece of data is inserted?
So I changed the trigger to the following:
Copy codeThe Code is as follows:
Alter TRIGGER [dbo]. [Table_a_Ins]
ON [dbo]. [Table_a]
AFTER INSERT
AS
BEGIN
Select ID from inserted;
END

Then execute the batch INSERT command above to see what values are stored in the inserted Table:
As expected, the result in the inserted Table is not a piece of data:

Knowing the cause of the error makes it easy to operate. We can create a cursor for the inserted Table and then edit each row of data inserted in batches through the cursor. The modified trigger code is as follows:
Copy codeThe Code is as follows:
Alter TRIGGER [dbo]. [Table_a_Ins]
ON [dbo]. [Table_a]
AFTER INSERT
AS
BEGIN
Declare @ ID int
Declare cur_Insert cursor
For
Select ID from inserted
Open cur_Insert
Fetch next from cur_Insert into @ ID
While @ fetch_status = 0
Begin
UPDATE Table_a
SET UpdateIDForTrigger = (@ ID + 10) -- in order to see the difference, the value 10 greater than the ID is directly assigned as the variable value.
Where id = @ ID;
Fetch next from cur_Insert into @ ID
End
Close cur_Insert
Deallocate cur_Insert
END

Then, we insert data in batches according to the above, and then query the results in the dynamic table:
Copy codeThe Code is as follows:
Insert into Table_a (Content)
Select Content from Table_Info;
Select * from Table_a;

At this time, no error message is displayed, and the running result is as follows:

 
In this way, the trigger can also be used when data is inserted in batches.
Then the code of the trigger was changed based on the suggestions of several predecessors. The cursor above is changed to the following method:
Copy codeThe Code is as follows:
Alter TRIGGER [dbo]. [Table_a_Ins]
ON [dbo]. [Table_a]
AFTER INSERT
AS
BEGIN
UPDATE Table_a
SET UpdateIDForTrigger = inserted. ID + 10
FROM inserted
Where Table_a.ID = inserted. ID
END

Then insert several rows of data in batches. The result is also acceptable. So there is no end to learning !!
 
Summary: The trigger is executed every time an Insert operation or Update or Delete operation is executed. Its object is not targeted at the number of modified rows (that is, it is executed when each row is modified ).