Hi guys,

This is the coment of one of my team member.

Team wants me to find a way to Update the columns of the table inside the trigger so that it is posible to mask some changes or format some values.

And even to deny changes they want to find a way so that the trigger can reverse changes quietly

I am working on this and need help to get this accomplished some how.

Thanks
DBFinder

Can you give us a more specific question? What is it exactly that you want to accomplish? Also please provide you DB2 and OS version.

Andy

Although it is neccesary to know more specific requirements as Andy wrote,
I thought that generated column and/or check constraint might be an alternative for some requirements. Like...
create view with hiding original column, revoke access on original table and grant access on view to needed users.

If you want to update columns in the same row, in a BEFORE trigger you may use NEW transition variable.

Actuall here is what our programmer want to do.

Table TRANS recieves a transaction via update or insert.
based on this transaction application another table taking data from TRANS table.
So that another table is ACH_TRANS. Now when application tries to update this table the trigger on this table (ACH_TRANS_UPDT) gets activated.

The programmer wants the trigger scan for some timestamp field .. say .. TMSTAMP to compare with today's date and redirect the update on the same table. And he says if NEW.TMSTMP are so and so he wants to stop the update and cause to rollback the update on table ACH_TRANS.

Other situation is that he wants to update same (ACH_TRANS) table with OLD values. Means no update has happend and no error or exception was signalled.

I tried few ways but did not suceeed yet.

The versions are 8.2.5 and 9.1 - on windows 2k3 servers - means two different production servers with exactly identical database table structures.

Any help is badly needed.

Thanks
DBFinder

Use a before update trigger. If the condition is met, do a SIGNAL. The application will have to do the rollback.

For this one, do another BEFORE update trigger. If the condition is met, set the new values to the old values.

Andy

Thanks ARWinner,

You are really winner. This worked beautifully.

Same Programmer is asking for SP for same bussiness logic.

How does a trigger compare with SP , performance wise ??

Thanks again,
DBFinder

About the same. You should see no difference in performance between the two.

Andy