Professional Web Applications Themes

Before update or delete trigger to insert ? - IBM DB2

Hi, I need some help...I'm fairly new to triggers and am attempting to duplicate a trigger in DB2 that already works in SQL Server. The basics are that I want to insert a row into an audit table using data currently in a table BEFORE an update is executed. In addition, I need to make sure the the record being updated is of a certain status so I need to do a count on third table to ensure this status. From what I've read, I can't do an insert in a BEFORE UPDATE trigger, but I don't know where to ...

  1. #1

    Default Before update or delete trigger to insert ?

    Hi,

    I need some help...I'm fairly new to triggers and am attempting to
    duplicate a trigger in DB2 that already works in SQL Server. The
    basics are that I want to insert a row into an audit table using data
    currently in a table BEFORE an update is executed. In addition, I need
    to make sure the the record being updated is of a certain status so I
    need to do a count on third table to ensure this status.
    From what I've read, I can't do an insert in a BEFORE UPDATE trigger,
    but I don't know where to go from here. I also have to do the same on
    a BEFORE DELETE. Any help would be greatly appreciated.

    My trigger looks like this:

    CREATE TRIGGER AUDIT_UPD_RBRT1
    NO CASCADE BEFORE UPDATE ON RB.RT1
    REFERENCING OLD AS OAUDIT
    FOR EACH ROW MODE DB2SQL
    WHEN ( (SELECT COUNT(*) FROM RB.RT_FLNG_ADPTN
    WHERE (IDL = OAUDIT.IDL AND
    IDS = OAUDIT.IDS AND
    STATUS_INDC = 'X')) = 1)
    INSERT INTO RB.RB_AUDIT_RT_DATA
    VALUES (OAUDIT.IDS,
    OAUDIT.IDL,
    CURRENT TIMESTAMP,
    1,
    11,
    OAUDIT.EXACT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    OAUDIT.RATE,
    'TESTING');


    Thanks,
    Cindy
    Cindy Gold Guest

  2. #2

    Default Re: Before update or delete trigger to insert ?

    Hi Cindy,

    To insert into the audit table you need to us an AFTER trigger, just as you
    do in SQL Server.
    (SQL server does not support BEFORE triggers)
    BEFORE triggers are efficient to do checking and for generating extra
    values.
    I.e. if you want to raise an error if the row does not comply with some
    rule, or you want to fill in soem data not provided by the SQL statement,
    they are the way to go.
    For example what you can do, is to use a before trigger for the checking and
    error handling and an after trigger for the auditing.
    W.r.t FOR EACH ROW vs FOR EACH STATEMENT:
    If you have an SQL Server (statement) trigger that joins the DELETED and
    INSERTED transition tables on the primary key to match them up, you probbaly
    want to turn it into a FOR EACH ROW trigger which is more efficient since
    teh NEW and OLD transition rows are already matched.

    Cheers
    Serge


    Serge Rielau Guest

  3. #3

    Default Re: Before update or delete trigger to insert ?

    Thank you very much Serge...I'll try all your suggestions.

    Cindy
    Cindy Gold Guest

Similar Threads

  1. I need a View, Insert, Update, Delete paradigm for usingflash forms
    By KJ Klosson in forum Coldfusion Flash Integration
    Replies: 2
    Last Post: November 6th, 12:28 PM
  2. ASP/VBS Using Command to Insert/Update/Delete
    By Sanjay in forum Dreamweaver AppDev
    Replies: 2
    Last Post: April 19th, 10:34 PM
  3. How to know if UPDATE, INSERT or DELETE succeed
    By Serge Myrand in forum ASP Database
    Replies: 7
    Last Post: July 19th, 01:56 AM
  4. SQL Update, Insert and Delete
    By Lou in forum ASP Database
    Replies: 1
    Last Post: June 22nd, 07:33 PM
  5. OOP page: Where to put update, insert, delete
    By jn in forum PHP Development
    Replies: 5
    Last Post: October 21st, 02:17 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139