Professional Web Applications Themes

Inserting data via Trigger (on different table) - MySQL

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

  1. #1

    Default Inserting data via Trigger (on different table)

    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 Guest

  2. #2

    Default Re: Inserting data via Trigger (on different table)

    ircmaxell wrote: 

    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 -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: Inserting data via Trigger (on different table)

    On May 1, 10:29 am, lark <net> wrote: 
    >
    > 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 -- net
    > To reply to me directly, delete "despam".[/ref]

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

    ircmaxell Guest

Similar Threads

  1. Replies: 2
    Last Post: March 8th, 11:57 AM
  2. Problem with inserting data into a table.....
    By Sharon in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: February 22nd, 12:37 PM
  3. Inserting data into a table
    By Geoff Wickens in forum ASP
    Replies: 1
    Last Post: August 25th, 10:06 AM
  4. Replies: 1
    Last Post: July 10th, 08:29 PM
  5. sending an email from a table trigger (before inserting row)
    By Holger Baer in forum Oracle Server
    Replies: 10
    Last Post: July 7th, 02:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139