SEO

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