Serge Rielau wrote:

> aka wrote:
> >"djjohnst" <djjohnst@gmail.com> wrote in message
> news:7749982a-28e7-471a-9422-e2681bf30e59@m36g2000hse.googlegroups.com
> ...
> > > Forgive my newbieness. I am created 2 tables EMP_DAN table and
> > > EMP_DAN_AUDIT. I am trying to create a trigger that anytime
> > > something is inserted or updated on the EMP_DAN table it will
> > > record a the change and timestamp on the AUDIT table. Here is the
> > > code i'm using which does not execute:
> > >
> > > CREATE TRIGGER NEW_TS1
> > > BEFORE UPDATE ON Emp_Dan
> > > REFERENCING OLD AS newTimeStamp
> > > FOR EACH ROW MODE DB2SQL
> > > BEGIN ATOMIC
> > > INSERT INTO EMP_DAN_AUDIT VALUES (newTimeStamp.EMPNO,
> > > newTimeStamp.FIRSTNME, newTimeStamp.MIDINIT,
> > > newTimeStamp.LASTNAME, newTimeSta
> > > mp.WORKDEPT, newTimeStamp.PHONENO, newTimeStamp.HIREDATE,
> > > newTimeStamp.JOB, newTimeStamp.EDLEVEL, newTimeStamp.SEX,
> > > newTimeStamp.BIR
> > > THDATE, newTimeStamp.SALARY, newTimeStamp.BONUS,
> > > newTimeStamp.COMM, current timestamp);
> > >
> > > It gives an error saying "An unexpected token "END-OF-STATEMENT"
> > > was found following ",
> > > current timestamp)". Expected tokens may include:
> > > "<delim_semicolon>"." Help me please...
> >
> > If the above is your complete create statement, then theres is
> > clearly an END missing (BEGIN ATOMIC ... END).
> Preempting the next question:
> Use
> --#SET TERMINATOR $
> to set the statement terminator to something else than ';' (in thsi
> case $. Then
> CREATE TRIGGER
> ..
> BEGIN ATOMIC
> ....;
> END
> $
> --#SET TERMINATOR ;

Alternatively, given that the trigger is only executing a single
statement, just remove the BEGIN ATOMIC (in which case you needn't
bother with END or changing the terminator). For example:

CREATE TRIGGER NEW_TS1
BEFORE UPDATE ON EMP_DAN
REFERENCING OLD AS O
FOR EACH ROW
INSERT INTO EMP_DAN_AUDIT VALUES (
O.EMPNO,
O.FIRSTNME,
O.MIDINIT,
O.LASTNAME,
O.WORKDEPT,
O.PHONENO,
O.HIREDATE,
O.JOB,
O.EDLEVEL,
O.SEX,
O.BIRTHDATE,
O.SALARY,
O.BONUS,
O.COMM,
CURRENT TIMESTAMP
);


Cheers,

Dave.