0

I need to convert sqlite triggers into mysql triggers. I've converted the following sqlite trigger

create trigger arpinsert after insert on arp
begin
    update arp set timeout = datetime('now', '60 seconds') where rowid = new.rowid and new.mac != '00:00:00:00:00:00';
    update arp set timeout = datetime('now')               where rowid = new.rowid and new.mac  = '00:00:00:00:00:00';
end;

into the following mysql trigger

delimiter //
create
    trigger arpBeforeInsert
    before insert
    on arp for each row
        begin
            if new.mac != '00:00:00:00:00:00' then
                set new.timeout = date_add(now(), interval 60 second);
            else
                set new.timeout = now();
            end if;
        end;//
delimiter ;

I know that the mysql trigger only triggers over the effected rows. Is the same true for the sqlite trigger? If I removed the where rowid = new.rowid would the sqlite trigger update over the whole table?

Michiel Ariens
  • 288
  • 4
  • 12

1 Answers1

2

The SQLite documentation says:

At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

However, this applies only to the trigger itself. Any SQL statements inside the trigger are independent (and must be so if you want to access other tables/rows). Therefore, executing an UPDATE without a WHERE clause will update all rows of the table, just the same as outside of the trigger.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • So if i remove the `where rowid = new.rowid` the triggers no longer have the same effect? If I want that kind of effect in mysql how would I go about it? – Michiel Ariens Jul 23 '15 at 07:41
  • If you really wanted to, you could execute the same UDPATE. – CL. Jul 23 '15 at 09:43
  • according to the discussion [here](http://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert) that doesn't work in mysql and indeed when I tried it I got an error. But looks like they have solved it as far as possible. Thanks for the help. – Michiel Ariens Jul 23 '15 at 11:40