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?
Thanks.

Yes (in DB2 UDB V8.1 LUW).

Andy

Example:

CREATE TRIGGER triggername
NO CASCADE BEFORE INSERT ON table
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET n.col = somevalue;
END

I have the last DB2 version but Express. Could this be the reason?

Not familiar with the version you describe. You may have to use an AFTER trigger (which is a drawback since you will be doing an extra transaction to the table).

Can you post your table structure and the statement you used ...

AFAIK, DB2 Express is functionally equivalent to other versions for most of the basic database stuff

Cheers
sathyaram

This is the table

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
begin
if :new.id <> ld.id then
Raiserror
end if;
if :new.timestamp < ld.timestamp then
Raiserror
else
select s_NMSBin.nextval into :new.timestamp from dual;
end if;
end;


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 )
BEGIN ATOMIC
SET n.timestamp = NEXTVAL for s_NMSBin.nextval;
END
@