Unanswered: Before update trigger. Can we update the New table?
Is it possible to update the NEW table in a before trigger?
I must update a column in a table if some conditions are satisfied.
In Oracle is posible to update this column using a before update trigger.
In sql server is not.
Can you tell me please if in Db2 it is possible such an update?
CREATE TABLE NMSBin ( id decimal(31, 0) IDENTITY (1, 1) NOT NULL ,
timestamp decimal(31, 0) NOT NULL,
workflow_id decimal(31, 0),
name nvarchar (100) NOT NULL ,
description nvarchar (500),
CONSTRAINT PK_NMSBin PRIMARY KEY (id)
In Oracle the trigger looks like this one:
CREATE OR REPLACE TRIGGER bu_NMSBin
BEFORE UPDATE ON NMSBin
FOR EACH ROW
if :new.id <> ld.id then
if :new.timestamp < ld.timestamp then
select s_NMSBin.nextval into :new.timestamp from dual;
First of all I test the new id if it.s ok.
Secondly I test the old timestamp if it's smaller than the new timestamp.
If it isn't I increment the new timestamp using a sequence.
Here is the update on :new pseudotable and in db2 I try and try and I could't update the new table (select s_NMSBin.nextval into :new.timestamp from dual
In SQL Server there aren't any before update triggers and it is not posible to update the inserted table, so I use only stored procedures instead of triggers.
Probably I will use the same method in DB2 unless you can help me and show me the way to update the new table.
Thank you very much for your support, it was and it is very precious for me.
CREATE TRIGGER triggername
NO CASCADE BEFORE UPDATE ON table
REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL
WHEN ( n.timestamp = o.timestamp )
SET n.timestamp = NEXTVAL for s_NMSBin.nextval;