Trigger 1

[cristi22]

I am trying to create a trigger(s) on insert / delete / update

The DAILY_PNR_LOG table should be updated with the key & the timestamp whenever the PNR_MEMBER table was updated / rows were deleted or inserted.

Platform: Aix
Database: UDB DB2 v9.1.300.257

Thanks a lot for you help!

 

[cristi22]

Here is what i've done so far:


create table trg_pnrmember_insert (rec varchar(1), rec_time timestamp ) ;

CREATE TRIGGER TRG_PNRM_INSERT AFTER INSERT
ON PNR_MEMBER FOR EACH STATEMENT MODE DB2SQL

BEGIN ATOMIC
insert into trg_pnrmember_insert values('1', current timestamp);

create table trg_pnrmember_delete (rec varchar(1), rec_time timestamp ) ;

CREATE TRIGGER TRG_PNRM_DEL AFTER DELETE ON PNR_MEMBER FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC
insert into trg_pnrmember_delete VALUES ('1', CURRENT TIMESTAMP);
...

is there a more eleganmt way to do it?
It works, but i'd like to put it all in one trigger, also is it possible to do it with the key updated?

thanks for all your help!
I am brand new with triggers....

 

[Farab]

I suggest you have a look at the SQL referrence guide, because there's a number of different ways to build up a TRIGGER clause.

- it is possible to create a single trigger which fires on INSERT, UPDATE and DELETE
- in such a case you would be able to reference OLD.column and NEW.column values (where [column] is any table column for the row in question)
- during INSERT, OLD.column will be null and NEW.column will contain the value for the new row.
- during UPDATE, OLD.column will contain the value before the update occured and NEW.column will contain the value after the update occured
- during DELETE, OLD.column will contain the value before the delete and NEW.column will be null
- it needs to be an AFTER trigger, which is what you have
- it needs to be an FOR EACH ROW trigger, which is not what you have
- I doubt you need it to be ATOMIC, but you can test that