0

I supposed to make an sql trigger for update that will only activate when the affected rows are greater than 0. Yet, Even though the affected row is 0 (or the new record is identical to the old record) it still fires the trigger. I want to check the number of affected rows, before i execute the insert query below. Thanks in advance! Sorry for my grammar!

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `dbase`.`table_update_trigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `table_update_trigger` AFTER UPDATE ON `table` 
    FOR EACH ROW BEGIN

IF (@@ROWCOUNT > 0) THEN
INSERT INTO 
    table_audit_trail
    (column1,
    column2,
    column3,
    column4,
    column5,
    column100) 
VALUES
    (old.column1,
    old.column2,
    old.column3,
    old.column4,
    old.column5,
    old.column100);
END IF;
END;
sephoy08
  • 1,104
  • 7
  • 16
  • What does "doesn't go well" mean? Please edit the question to say exactly what happens and what you want to happen. – Andrew Leach May 30 '12 at 07:45
  • 1
    I'm not that up on MySQL, but I'd have thought a trigger defined as `FOR EACH ROW` would only fire... if there are affected rows. If there are no affected rows, how many times would this trigger fire? – Damien_The_Unbeliever May 30 '12 at 07:46
  • I read your question, but sadly it doesn't go well. Please help me with this! *(By giving error messages, documenting expected and unexpected behaviour, etc, etc. We're not psychic, sorry.)* – MatBailie May 30 '12 at 08:00
  • sorry for my grammar, i modify my question to be more precise on what i want to happen. Thanks! – sephoy08 May 30 '12 at 08:08

1 Answers1

0

There is no @@ROWCOUNT in MySQL. You can read this post to find ount how to replace it.

But you dont need that. Your trigger is for each row so it will fire for every updated row. (But that doesnt mean that your rows have changed. Just that they were updated by some statement.)

Community
  • 1
  • 1
Grzegorz W
  • 3,487
  • 1
  • 21
  • 21
  • Oh i see, but what if i don't know the query executed? yet i want to know if there are changes that made. – sephoy08 May 30 '12 at 07:58
  • Compare each column between `old` and `new` – Grzegorz W May 30 '12 at 08:04
  • well that's my first choice but, i have 400 columns to compare. Is there another way to check if there are changes? Thanks! – sephoy08 May 30 '12 at 08:10
  • read [this post](http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed) – Grzegorz W May 30 '12 at 08:15
  • i try adding column ts, type timestamp and not null, However, this column doesn't change whenever I'm updating a record. Im using SQLyog Enterprise in executing my query. – sephoy08 May 30 '12 at 10:22