On May 1, 10:29 am, lark <ham...@sbcdeglobalspam.net> wrote:
> ircmaxell wrote:
> > Ok, let me start off by saying I know almost nothing about triggers.
> > We have a large (local development) database that needs to be synced
> > with a remote (live) database from time to time. Rather than dumping
> > and reinstalling the database each time (as we are doing now), I want
> > to create a new table to keep track of the changes (so we can derive
> > queries from that). We are using 5.0.24a-community-nt. Here's the
> > SQL I tried... I keep geting a 1064 (Syntax error) error at
> > 'INSERT'... Any ideas?
> > DELIMITER //
> > CREATE TRIGGER `universal`.`auth_in` AFTER INSERT ON
> > `universal`.`author`
> > FOR EACH ROW
> > BEGIN
> > INSERT INTO `univ_changes`.`author`
> > SET act = 'insert', new_id = NEW.id, new_name = NEW.name;
> > END;//
> > CREATE TRIGGER `universal`.`auth_up` AFTER UPDATE ON
> > `universal`.`author`
> > FOR EACH ROW
> > BEGIN
> > INSERT INTO `univ_changes`.`author`
> > SET act = 'update', new_id = NEW.id, new_name = NEW.name, old_id =
> > OLD.id, old_name = OLD.name;
> > END;//
> > CREATE TRIGGER `universal`.`auth_del` AFTER DELETE ON
> > `universal`.`author`
> > FOR EACH ROW
> > BEGIN
> > INSERT INTO `univ_changes`.`author` SET act = 'delete', old_id =
> > OLD.id, old_name = OLD.name;
> > END;//
> > DELIMITER ;
>
> as far as i know and seen, triggers operate on the same table they're
> written for. so for example if create a trigger on the author table, it
> can update a field within that table but not any other table. that's why
> you're getting the insert error.
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

Something I read somewhere else (I don't remember where) was that a
trigger could NOT operate on the same table... I don't know...