CODEWITHSUNDEEP
mysqlsqltriggersdatabase
user123
user123

Reputation: 5407

Perform insert or update on table using trigger

I am trying to fetch some values from php page and perform trigger in mysql database. Which is as below:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `test`.`MysqlTrigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `MysqlTrigger` AFTER INSERT OR UPDATE ON `table2` 
    FOR EACH ROW BEGIN
    IF(NEW .flag=0) THEN
    INSERT INTO temp(sent,pcount,ncount) VALUES (NEW.sent,NEW.pcount,NEW.ncount);
    ELSE
        UPDATE temp SET pcount=NEW.pcount AND ncount=NEW.ncount WHERE id = NEW.id;
    END IF; 
    END;
$$

DELIMITER ;

Which gives error:

error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'or update ON `table2` 
    FOR EACH ROW BEGIN
    if(NEW .flag=0) then
    INSERT INTO' at line 3

I tried to change = with =: but error persist. Any idea whaat the prob?

Upvotes: 1

Views: 840

Answers (2)

user123
user123

Reputation: 5407

I count do it. There was problem in syntax. Here is the correct which perform update insert at same moment in different table and update-inset on same table but with if else:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `test`.`MysqlTrigger2`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `MysqlTrigger2` AFTER INSERT ON `table2` 
    FOR EACH ROW BEGIN
    IF 'flag'=1 THEN
        UPDATE record SET sent=NEW.sent WHERE id=NEW.id;
    ELSE
        INSERT INTO record VALUES (id,NEW.sent);
        INSERT INTO temp VALUES ()
    END IF;
    END;
$$

DELIMITER ;

Upvotes: 0

juergen d
juergen d

Reputation: 204784

You can't define a trigger for update and insert at the same time. You have to define 2 triggers:

CREATE TRIGGER `Mysql_insert_Trigger` AFTER INSERT ON `table2` ...
CREATE TRIGGER `Mysql_update_Trigger` AFTER UPDATE ON `table2` ...

Upvotes: 1

Related Questions