Trouble creating triggers

[Ted]

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]

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

[Bill Karwin]

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.

[Ted]

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]

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

[Bill Karwin]

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.