Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Create Trigger on Update of Tables

    Hi,

    I have three tables :

    StatusTable --> has columns empid, haschanged

    EmpTable --> has columns empid, empname, descr

    SalaryTable --> has columns empid, salary

    I want to create a trigger that updates the column 'haschanged' in the Status table only if the values of 'empname', 'descr' or 'salary' change after an update.

    what's the best way to do this? thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, just create an AFTER UPDATE trigger. What exactly is the problem there?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    So far I have this and it works -->

    CREATE TRIGGER EmpUpdate_tgr
    AFTER UPDATE OF empname, descr ON EmpTable
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
    WHEN( (N.empname != O.empname) OR (N.descr != O.descr) )
    UPDATE StatusTable SET haschanged = 't' WHERE empid = N.empid;

    But how can I include the Salary table as a part of this trigger? Is it possible to do that?

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2user
    how can I include the Salary table as a part of this trigger? Is it possible to do that?
    No, you'll need two triggers, one on each table.
    Updating the "haschanged" flag twice shouldn't be problematic, I suppose.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    No, changing the 'haschanged' flag twice isn't a problem at all... how about nested triggers? What are they used for...could they be used in this case? Thanks!

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2user
    How about nested triggers? What are they used for...could they be used in this case?
    In this case you want to trigger two different actions, viz. updates in EmpTable and updates in SalaryTable. For that purpose you'll need at least two triggers.
    Since two fairly simple triggers will do the job, I don't see any need for more complex triggers.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Dec 2002
    Posts
    123
    thank you.. i created two triggers and it works just fine..

Posting Permissions

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