Just a quick question that no doubt someone out there will know the answer to.
I need to be able to do multiple insert/updates within a trigger. Every attempt ends with failure :(
DROP TRIGGER IF EXISTS `Insert_Article`//
CREATE TRIGGER `Insert_Article` AFTER INSERT ON `Article`
FOR EACH ROW insert into FullTextStore (`Table`, `PrimaryKey`, `ColumnName`, `Data`, `Created`) values ('Article', NEW.ArticleID, 'Description', NEW.Description, UNIX_TIMESTAMP())
//
At the moment, the above simply inserts a row into a table when the parent table inserts. This works fine.
To get this to work with mulitple values I need to do
DROP TRIGGER IF EXISTS `Insert_Article`//
CREATE TRIGGER `Insert_Article` AFTER INSERT ON `Article`
FOR EACH ROW insert into FullTextStore (`Table`, `PrimaryKey`, `ColumnName`, `Data`, `Created`)
select 'Article', NEW.ArticleID, 'Description', NEW.Description, UNIX_TIMESTAMP()
union
select 'Article', NEW.ArticleID, 'Keywords', NEW.Keywords, UNIX_TIMESTAMP()
//
But... There must be an easier way? When I try using ; to terminate each statement, it fails with
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL version for the right syntax to use near 'select 'Article', NEW.ArticleID, 'Keywords', 'NEW.Keywords, UNIX_TIMESTAMP())' at line 1
I can't even get multiple update statements to work.
It'd be a great help if anyone could point out what i'm doing wrong?
Cheers
Gavin
From the docs: Create Trigger Syntax
trigger_stmt is the statement to execute when the trigger activates. If you want to execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are allowable within stored routines
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With