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

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

Good luck,
aka.

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 ;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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.