Hi Fellas..I am writing a trigger to insert values before update on one table to log the old records in a history table but the trigger is not getting created
The trigger statement is given below
CREATE TRIGGER InsertBefore
BEFORE UPDATE ON DB2INST1.STATUS
REFERENCING
OLD AS o NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN
IF (o.STATUS <> n.STATUS OR o.TARGET <> n.TARGET) THEN
Insert into DB2INST1.STATUSHISTORY
(HISTORYID,
STATUS,
TARGET,
CREATEDDATE)
VALUES
(
SELECT MAX(HISTORYID)+1 FROM DB2INST1.STATUSHISTORY),
o.STATUS,
o.TARGET,
CURRENT TIMESTAMP)
ELSEIF (O.SUBTARGET <> N.SUBTARGET OR O.SUBSTATUS <> N.SUBSTATUS) THEN
Insert into DB2INST1.STATUSHISTORY
(HISTORYID,
STATUS,
TARGET,
CREATEDDATE)
VALUES
(
SELECT MAX(HISTORYID)+1 FROM DB2INST1.STATUSHISTORY),
o.STATUS,
o.TARGET,
CURRENT TIMESTAMP)

END IF;

END

Not sure where I am going wrong. Please help. I am using db29.7 on aix 5.3

Thanks

CitiDibi

Ok I got to a point where I can create an AFTER TRIGGER but as soon as I change it to BEFORE it throws me an error. Not sure what I am thinking is correct approach. Will wait for you all to recommend. Thanks a lot for any suggestion

Thanks

Citidibi

What error message(s)(including error code and full message text) did you got?

Hi there Tonkuma. Thanks for replying to my post
Below is what I was getting
SQL0797N The trigger "DB2INST1.INSERTBEFORE" is defined with an unsupported triggered SQL statement. SQLSTATE=42987

And thats when I tried with AFTER which worked. Do you think I have a workaround?

Thanks

Citidibi

No semicolons for Insert statements.

Please show us complete create statement and the message which showed successful.

SQL0104N this is what I got with the semicolon

wrote a simple trigger on a test table with when which worked

CREATE TRIGGER INSERTAFTER
AFTER UPDATE ON DB2INST1.STATUS
REFERENCING OLD as O NEW AS N
FOR EACH ROW
when (O.TARGET <> N.TARGET )
Insert into DB2inst1.TEST_HISTORY
(
CREATEDDATE)
VALUES
(
CURRENT TIMESTAMP);

when the same simple logic is raN with the BEFORE statement, I get this


CREATE TRIGGER BEFOREINSERT
BEFORE UPDATE ON DB2INST1.STATUS
REFERENCING OLD as O NEW AS N
FOR EACH ROW
when (O.TARGET <> N.TARGET )
Insert into DB2inst1.TEST_HISTORY
(
CREATEDDATE)
VALUES
(
CURRENT TIMESTAMP);


DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0797N The trigger "DB2INST1.BEFOREINSERT" is defined with an unsupported
triggered SQL statement. LINE NUMBER=5. SQLSTATE=42987

SQL0797N

See this statement.
So, try with adding BEGIN, END(that makes an SQL compound (compiled) statement), and statement-termination-character("@"), like...

CREATE TRIGGER BEFOREINSERT
BEFORE UPDATE ON DB2INST1.STATUS
REFERENCING OLD as O NEW AS N
FOR EACH ROW
when (O.TARGET <> N.TARGET )
BEGIN
Insert into DB2inst1.TEST_HISTORY
(
CREATEDDATE)
VALUES
(
CURRENT TIMESTAMP);
END@

hi,

send the value for existing record and new .

What fixpack are you at?