SEO

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 ;

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 -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

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...