Professional Web Applications Themes

Trouble creating triggers - MySQL

Here is the code I tried: delimiter $; CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses FOR EACH ROW BEGIN SET NEW.interview_date = CURRENT_TIMESTAMP; END$ delimiter ; I adapted this from an example on page 207 in the third edition of "MySQL: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0" by Paul DuBois. When submitted using MySQL Query Browser, I get an error 1064, complaining about the last line shown. I was going to add this trigger to each of my archive tables, and then use an insert statement of the following form to keep the archives ...

  1. #1

    Default Trouble creating triggers

    Here is the code I tried:

    delimiter $;
    CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses
    FOR EACH ROW BEGIN
    SET NEW.interview_date = CURRENT_TIMESTAMP;
    END$
    delimiter ;

    I adapted this from an example on page 207 in the third edition of
    "MySQL: The definitive guide to using, programming, and administering
    MySQL 4.1 and 5.0" by Paul DuBois.

    When submitted using MySQL Query Browser, I get an error 1064,
    complaining about the last line shown.

    I was going to add this trigger to each of my archive tables, and then
    use an insert statement of the following form to keep the archives up
    to date.

    INSERT INTO archive_t
    SELECT * FROM t
    WHERE .....

    Of course, the where clause will be structured to ensure only the
    current data being stored in the main tables (using either insert or
    update statements) will be added to the archive.

    The tables archive_t and t are identical, except for the engine
    (ARCHIVE and InnoDB respectively) and the fact archive_t has the extra
    field "interview_date"

    I want to make sure I have this right before writing the code for the
    several dozen archive tables.

    For the applications where I am doing this, the first purpose of the
    archive tables is to ensure there is a complete record of all data
    entered, for the purposes of supporting accurate audits. And for some,
    there is an additional purpose of supporting monthly and annual summary
    statistics.

    DuBois says that triggers can't refer to tablenames, and rightly points
    out that this limits their utility. If he is wrong (or has been made
    wrong by changes made to MySQL 5 since his book went to press), then
    I'd add triggers to the main tables that insert the data provided to
    them into the archive tables.

    Three questions:

    1) What is wrong with the SQL statements above that create my triggers?

    2) Is there a faster way to create the triggers I need for all several
    dozen archive tables; faster than just typing similar code for each
    table?

    3) Is there a better way to achieve my purpose?

    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Trouble creating triggers

    Here is the code I tried:

    delimiter $;
    CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses
    FOR EACH ROW BEGIN
    SET NEW.interview_date = CURRENT_TIMESTAMP;
    END$
    delimiter ;

    I adapted this from an example on page 207 in the third edition of
    "MySQL: The definitive guide to using, programming, and administering
    MySQL 4.1 and 5.0" by Paul DuBois.

    When submitted using MySQL Query Browser, I get an error 1064,
    complaining about the last line shown.

    I was going to add this trigger to each of my archive tables, and then
    use an insert statement of the following form to keep the archives up
    to date.

    INSERT INTO archive_t
    SELECT * FROM t
    WHERE .....

    Of course, the where clause will be structured to ensure only the
    current data being stored in the main tables (using either insert or
    update statements) will be added to the archive.

    The tables archive_t and t are identical, except for the engine
    (ARCHIVE and InnoDB respectively) and the fact archive_t has the extra
    field "interview_date"

    I want to make sure I have this right before writing the code for the
    several dozen archive tables.

    For the applications where I am doing this, the first purpose of the
    archive tables is to ensure there is a complete record of all data
    entered, for the purposes of supporting accurate audits. And for some,
    there is an additional purpose of supporting monthly and annual summary
    statistics.

    DuBois says that triggers can't refer to tablenames, and rightly points
    out that this limits their utility. If he is wrong (or has been made
    wrong by changes made to MySQL 5 since his book went to press), then
    I'd add triggers to the main tables that insert the data provided to
    them into the archive tables.

    Three questions:

    1) What is wrong with the SQL statements above that create my triggers?

    2) Is there a faster way to create the triggers I need for all several
    dozen archive tables; faster than just typing similar code for each
    table?

    3) Is there a better way to achieve my purpose?

    Thanks,

    Ted

    Ted Guest

  3. #3

    Default Re: Trouble creating triggers

    Ted wrote:
    > Here is the code I tried:
    >
    > delimiter $;
    > CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses
    > FOR EACH ROW BEGIN
    > SET NEW.interview_date = CURRENT_TIMESTAMP;
    > END$
    > delimiter ;
    I got this to work, but without putting the semicolon on the delimiter
    line. So: "delimiter $" instead of "delimiter $;". I find it strange
    that one does not put the current delimiter at the end of the statement
    to set the new delimiter, but it seems to work.

    Of course, make sure you are using MySQL 5.0. Triggers are not
    implemented in MySQL 4.1.
    > 2) Is there a faster way to create the triggers I need for all several
    > dozen archive tables; faster than just typing similar code for each
    > table?
    Write a script in a text editor, use copy & paste to duplicate the
    triggers, and substitute the table names as needed.
    > 3) Is there a better way to achieve my purpose?
    If it's just timestamps you're updating, you might be able to use
    automated timestamp updating built into MySQL. Read this page:
    [url]http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Trouble creating triggers

    Here is the code I tried:

    delimiter $;
    CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses
    FOR EACH ROW BEGIN
    SET NEW.interview_date = CURRENT_TIMESTAMP;
    END$
    delimiter ;

    I adapted this from an example on page 207 in the third edition of
    "MySQL: The definitive guide to using, programming, and administering
    MySQL 4.1 and 5.0" by Paul DuBois.

    When submitted using MySQL Query Browser, I get an error 1064,
    complaining about the last line shown.

    I was going to add this trigger to each of my archive tables, and then
    use an insert statement of the following form to keep the archives up
    to date.

    INSERT INTO archive_t
    SELECT * FROM t
    WHERE .....

    Of course, the where clause will be structured to ensure only the
    current data being stored in the main tables (using either insert or
    update statements) will be added to the archive.

    The tables archive_t and t are identical, except for the engine
    (ARCHIVE and InnoDB respectively) and the fact archive_t has the extra
    field "interview_date"

    I want to make sure I have this right before writing the code for the
    several dozen archive tables.

    For the applications where I am doing this, the first purpose of the
    archive tables is to ensure there is a complete record of all data
    entered, for the purposes of supporting accurate audits. And for some,
    there is an additional purpose of supporting monthly and annual summary
    statistics.

    DuBois says that triggers can't refer to tablenames, and rightly points
    out that this limits their utility. If he is wrong (or has been made
    wrong by changes made to MySQL 5 since his book went to press), then
    I'd add triggers to the main tables that insert the data provided to
    them into the archive tables.

    Three questions:

    1) What is wrong with the SQL statements above that create my triggers?

    2) Is there a faster way to create the triggers I need for all several
    dozen archive tables; faster than just typing similar code for each
    table?

    3) Is there a better way to achieve my purpose?

    Thanks,

    Ted

    Ted Guest

  5. #5

    Default Re: Trouble creating triggers

    This is a day for weird behaviour. I see my note appearing three times
    now, and yet I got error messages from the server two times saying it
    hadn't been posted.

    Anyway, thanks Bill.

    The errors I encountered, then, must be something odd in how MySQL
    Query Browser handles this. When I do to the command line, and use the
    client app. "mysql", the code is accepted and seems to work. However,
    I get errors on the INSERT.

    I set up a couple dummy tables in the test DB, and populated dummy with
    three records.

    dummy2 has two columns:dummy_id and name.

    dummy has three columns, two identical to dummy2, and the third is
    mydate

    mysql> INSERT INTO dummy SELECT name FROM dummy2;
    ERROR 1136 (21S01): Column count doesn't match value count at row 1

    I get the same error if I use "SELECT * FROM dummy2" as my SELECT
    clause.

    This should have worked, I think, because dummy_id is autoincremented
    in both tables. Adding dummy_id to the SELECT clause changed nothing.
    I still get the same error. Why?

    And while the automated timestamp updating built into MySQL looks
    appealing, it too give me trouble, as the following copy of my code and
    resultant output shows.

    mysql> CREATE TABLE `test`.`dumm3` (
    -> `dummy_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    -> `name` VARCHAR(45) NOT NULL DEFAULT '',
    -> `mydate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY(`dummy_id`)
    -> )
    -> ENGINE = InnoDB;
    ERROR 1067 (42000): Invalid default value for 'mydate'
    mysql> CREATE TABLE `test`.`dumm3` (
    -> `dummy_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    -> `name` VARCHAR(45) NOT NULL DEFAULT '',
    -> `mydate` DATETIME NOT NULL DEFAULT NOW(),
    -> PRIMARY KEY(`dummy_id`)
    -> )
    -> ENGINE = InnoDB;
    ERROR 1067 (42000): Invalid default value for 'mydate'
    mysql> CREATE TABLE `test`.`dumm3` (
    -> `dummy_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    -> `name` VARCHAR(45) NOT NULL DEFAULT '',
    -> `mydate` DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY(`dummy_id`)
    -> )
    -> ENGINE = InnoDB;
    ERROR 1067 (42000): Invalid default value for 'mydate'

    What have I missed?

    Thanks,

    Ted

    Ted Guest

  6. #6

    Default Re: Trouble creating triggers

    Ted wrote:
    > dummy2 has two columns:dummy_id and name.
    >
    > dummy has three columns, two identical to dummy2, and the third is
    > mydate
    >
    > mysql> INSERT INTO dummy SELECT name FROM dummy2;
    > ERROR 1136 (21S01): Column count doesn't match value count at row 1
    >
    > I get the same error if I use "SELECT * FROM dummy2" as my SELECT
    > clause.
    >
    > This should have worked, I think, because dummy_id is autoincremented
    > in both tables. Adding dummy_id to the SELECT clause changed nothing.
    > I still get the same error. Why?
    See [url]http://dev.mysql.com/doc/refman/5.0/en/insert.html:[/url]

    "If you do not specify a list of column names for INSERT ... VALUES or
    INSERT ... SELECT, values for every column in the table must be provided
    by the VALUES list or the SELECT statement."

    In other words, the two statements are equivalent:

    INSERT INTO dummy SELECT name FROM dummy2;
    INSERT INTO dummy (dummy_id, name) SELECT name FROM dummy2;

    Clearly the number of columns don't match in this example. If you
    specify a list consisting of a subset of the columns, it should work:

    INSERT INTO dummy (name) SELECT name FROM dummy2;
    > -> `mydate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    > -> `mydate` DATETIME NOT NULL DEFAULT NOW(),
    > -> `mydate` DATETIME DEFAULT CURRENT_TIMESTAMP,
    > ERROR 1067 (42000): Invalid default value for 'mydate'
    >
    > What have I missed?
    The special default properties of the TIMESTAMP datatype only work for
    TIMESTAMP. DATETIME is a different datatype, and does not share those
    special default properties.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Creating database triggers through CFQUERY?
    By joeDangelo in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 4th, 10:49 AM
  2. Trouble Creating CFMX War Files for OSX
    By Goddess1220 in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: February 22nd, 02:46 PM
  3. Trouble creating PDFs from Freehand
    By Sabahat_Muhammad@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: May 20th, 01:28 PM
  4. Trouble with mouse triggers
    By jodasa webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 1
    Last Post: November 14th, 07:57 PM
  5. Trouble creating a new .NET webservice
    By smile in forum ASP.NET Web Services
    Replies: 1
    Last Post: September 23rd, 02:16 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