Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    57

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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes (in DB2 UDB V8.1 LUW).

    Andy

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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

  4. #4
    Join Date
    Jun 2004
    Posts
    57

    it doesn't work!

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

  5. #5
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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).

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jun 2004
    Posts
    57
    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.

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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
    @

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •