• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

DB2 trigger

Hi,

I am trying to create this trigger that is updating the time and data in a table when a flag is changed in another table. This is the trigger.

The erros is : This statemens is not allowed.

Any ideas how can i make it work?

Thanks.
CREATE TRIGGER name
 
	BEFORE UPDATE ON table
 
	REFERENCING OLD AS OLDVAL 
 
	NEW AS NEWVAL 
 
	FOR EACH ROW 
 
	MODE DB2SQL 
 
	
 
	when (OLDVAL.flag != NEWVAL.flag )
 
		UPDATE other_table
 
				SET (FEADAT,FEATIM) = (CURRENT DATE, SUBSTR(CAST(current time AS CHAR(8)),1,2) ||  SUBSTR(CAST(current time AS CHAR(8)),4,2) )
 
					WHERE other_table.id =NEWVAL.id ;

Open in new window

0
se_1581
Asked:
se_1581
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi se,

I believe that this kind of trigger needs to be an AFTER trigger.  


Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

I agree with Kent. It should be an AFTER trigger.

Also, I think you need a BEGIN and END.

e.g.
 create trigger TR_MyTableAfterUpdate
   after update of flag on MyTable
   referencing new row as NewRow
               old row as OldRow
   for each row
   set option dbgview = *source
 Begin
    update SomeOtherTable s
    .... < some stuff >
    ;
 End;

Open in new window

0
 
se_1581Author Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now