When we want to update a dynamic table (that is, the data in the table is constantly added), maybe we'll use a database broker, write a job, and then let him periodically query the most recently added data in the dynamic table and then update the data. This enables the updating of data, but the data is not synchronized in real time.
At this point, the trigger is the artifact we want. We can create a new trigger on that dynamic table. The essence of a trigger is a stored procedure, except that the time he invokes is executed based on the table that the dynamic table is built on (that is, insert new data, update or delete data).
Concrete how to use triggers, today I do not introduce, the garden is very much information. So what do I want to introduce today?
A few days ago, when I wrote the SQL code, I stumbled across this problem: I always thought that whenever a data is inserted into a dynamic table, the trigger executes once, but as I understand it, the number of times the trigger executes is the same as the number of rows inserted when the data is inserted in bulk, but that is not the case. Take a bit of time today, just want to write out and share with you, said the wrong, please everyone treatise!
Below, I have written a simple example for your reference.
Copy Code code as follows:
--we're going to build a dynamic table of triggers
Create Table Table_a
(
ID int identity (1,1),--self-Increasing ID
Content nvarchar (50),
Updateidfortrigger int
)
Then we create a trigger on the table
Copy Code code 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 the Table_a table to see the effect of real-time execution more clearly
UPDATE table_a
SET Updateidfortrigger = (@ID +10)--to be able to see the difference, it directly assigns a value of 10 greater than the ID to the variable
WHERE ID = @ID;
End
Next, we test the results by inserting the common strips:
Copy Code code as follows:
--Add data to the information table
Insert into table_a (content) VALUES (' Information one ');
Insert into table_a (content) VALUES (' Information II ');
Then query the current data in the dynamic table
Copy Code code as follows:
Query results as shown in figure:
We can see that the trigger executes. The trigger performs the update function at the same time as each data is inserted.
Then, we're going to bulk insert the data, and in order for us to insert, we'll create a temporary basic information table:
Copy Code code as follows:
--Basic Information table
Create Table Table_info
(
ID int Identity (1,1),
Content nvarchar (50)
)
And then insert the data
Copy Code code as follows:
Insert into Table_info (content) VALUES (' Information three ');
Insert into Table_info (content) VALUES (' Information four ');
Insert into Table_info (content) VALUES (' Information five ');
Insert into Table_info (content) VALUES (' Information six ');
Insert into Table_info (content) VALUES (' Information seven ');
Insert into Table_info (content) VALUES (' Information eight ');
Insert into Table_info (content) VALUES (' Information nine ');
Insert into Table_info (content) VALUES (' Information 10 ');
Then we can insert the data into the dynamic table in bulk.
Copy Code code as follows:
Insert into table_a (Content)
Select Content from Table_info
Here's the point, we'll get an error in the message box when we execute this SQL statement:
Experienced friends will know that this error is caused by multiple results assigned to a variable with a "=" value.
That is: SET @ variable = (select multiple rows result from Table)
At this time, I was puzzled, what is the problem? Isn't a trigger executed every time you plug in a piece of data?
So, I changed the trigger:
Copy Code code as follows:
Alter TRIGGER [dbo]. [Table_a_ins]
on [dbo]. [Table_a]
After INSERT
As
BEGIN
Select ID from inserted;
End
Then perform the bulk insert above to see what value he inserted in the table:
As expected, the results in the inserted table are not a single piece of data:
Knowing the cause of the error, we can do it simply by building a cursor on the inserted table and then editing each row of data in the batch insert by Grand. Here is our modified trigger code:
Copy Code code 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)--to be able to see the difference, it directly assigns a value of 10 greater than the ID to the variable
WHERE ID = @ID;
FETCH NEXT from Cur_insert into @ID
End
Close Cur_insert
Deallocate Cur_insert
End
We then insert the data in the batch above, and then query the results in the dynamic table below:
Copy Code code as follows:
Insert into table_a (Content)
Select Content from Table_info;
SELECT * from Table_a;
There are no errors to run at this time, and the results are as follows:
In this way, the trigger is also available for bulk inserts when inserting data.
Then combined with the recommendations of several predecessors, and then changed the trigger code. Change the above cursor to the following way:
Copy Code code 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 a few rows of data are inserted, and the result is OK. So it's a life-long learning!!
Summary of: A trigger run is performed every time an insert operation or a update,delete operation is performed. Its object is not for the number of modified rows (that is, when each row is modified).
