MySQL Trigger – How to Get Value of Last Updated Row from a Table

MySQLtrigger

I want to insert the last updated row into a new table using a trigger?

How to fetch that row if any of the column (value) from that table has been updated or changed?

I am using this code:

CREATE TRIGGER database.tbl1_AFTER_UPDATE 
    AFTER UPDATE ON tbl1 
FOR EACH ROW 
BEGIN 
    INSERT INTO tbl2 (c1,c2,c3) 
    SELECT c1,c2,c3 
    from tbl1; 
END

But this is inserting all the rows from tbl1 into tbl2. I want only the updated rows to be inserted into tbl2.

How can I do it?

Best Answer

From http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.

So if you want the new rows being inserted on another table, you should use the syntax:

INSERT INTO tbl2 (c1, c2, c3) VALUES (NEW.c1, NEW.c2, NEW.c3);

Related Solutions

Sql-server – How to get last identity row Inserted when using instead of trigger

With an INSTEAD_OF trigger it means no insert occurred yet. You cannot know the identity since it wasn't yet generated. Sneaking the value from metadata is possible (DBCC CHECKIDENT) but relying on it will not work correctly under concurrency and besides it requires elevated privileges.

INSTEAD_OF triggers are extremely seldom required and a serious code smell. Are you sure you need it? Can't you do the work with a regular AFTER trigger?

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Sql-server – How to get last identity row Inserted when using instead of trigger

How to commit transaction on an after update event trigger

Related Question