I want to build a trigger, if after the test a row in the table is updated, the bank's FLG fields into a "update", how to write? He had tried to write a, not to!
DROP the TRIGGER IF the EXISTS test_trigger_update;
The CREATE test_trigger_update
AFTER the update ON the test
FOR EACH ROW
The BEGIN
Update the test set FLG='updated' where id=old. Id.
END

CodePudding user response:

Whether changes in a table, only to another table for record

CodePudding user response:

The alter table tt add uptime timestamp on the update current_timestamp.
Can't you add a column for it; Record the last modified time,

Your trigger usage has a problem, if this update the undead cycle?

CodePudding user response:

 drop table if the exists test; 
Create table test (
Id int primary key, 
FLG nvarchar (20) 
); 
Insert into test values (1, "); 
Insert into test values (2, "); 

Drop the trigger if the exists trig_test_update; 
Delimiter $$
CREATE the TRIGGER ` trig_test_update ` 
Before the update on ` test ` for each row 
The BEGIN 
Set new. FLG='updated'; 
END; 
$$

Update the test set FLG='a'; 

Select * from the test; 
/* 
+ - + -- -- -- -- -- -- -- -- + 
| | id FLG | 
+ - + -- -- -- -- -- -- -- -- + 
| | have update | 1 
| 2 | have update | 
+ - + -- -- -- -- -- -- -- -- + 
*/

CodePudding user response:

You update this list of SQL statements, plus flag=have update not line? Trigger itself has a problem, maybe death cycle,

CodePudding user response:

This device if use departure could really into an infinite loop, has been performing the update. But if you add a field, the alter table tt add uptime timestamp on the update current_timestamp, and I am according to your way of thinking to write a trigger and encounter a problem, you leave such a device, that is, each data update only one effective, if repeat the update, update FLG field has been shown, that will not show the effect