Professional Web Applications Themes

question about trigger - IBM DB2

Hello, I would like to use trigger to record change in every field (F1, F2, F3..) from table TAB1. CREATE TRIGGER TR1 AFTER UPDATE OF F1, F2, F3, F4 ON TAB1 REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO TAB2 VALUES (F1 OR F2 ??) END How can I find name of column which was actually changed ? Regards Piotr...

  1. #1

    Default question about trigger

    Hello,

    I would like to use trigger to record change in every field (F1, F2, F3..)
    from table TAB1.

    CREATE TRIGGER TR1
    AFTER UPDATE OF F1, F2, F3, F4 ON TAB1
    REFERENCING
    NEW AS N
    OLD AS O
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO TAB2 VALUES (F1 OR F2 ??)
    END

    How can I find name of column which was actually changed ?

    Regards

    Piotr




    Piotr Guest

  2. #2

    Default Re: question about trigger

    Piotr <m-l-o-d-ytlen.pl> wrote:
    > Hello,
    >
    > I would like to use trigger to record change in every field (F1, F2, F3..)
    > from table TAB1.
    >
    > CREATE TRIGGER TR1
    > AFTER UPDATE OF F1, F2, F3, F4 ON TAB1
    > REFERENCING
    > NEW AS N
    > OLD AS O
    > FOR EACH ROW MODE DB2SQL
    > BEGIN ATOMIC
    > INSERT INTO TAB2 VALUES (F1 OR F2 ??)
    > END
    >
    > How can I find name of column which was actually changed ?
    Two easy options:
    (1) use separate triggers, one for each column
    (2) compare the new and old values - if the values are different, then
    that's where the update happened.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  3. #3

    Default Re: question about trigger

    > Two easy options:
    > (1) use separate triggers, one for each column
    > (2) compare the new and old values - if the values are different, then
    > that's where the update happened.
    Hello,
    I would rather second option but I wonder if there is possibility to make it
    more "elegant"
    This is sample code ilustrating my idea:

    DECLARE tname CHAR(40);
    SET tname = 'tab1'
    FOR kols AS SELECT colname FROM syscat.columns WHERE tabname = tname
    DO
    IF n.kols <> o.kols THEN

    --do something

    END IF
    END FOR

    Is it possible to make it this way ?

    Regards
    Piotr


    Piotr Guest

  4. #4

    Default Re: question about trigger

    Piotr wrote:
    >>Two easy options:
    >>(1) use separate triggers, one for each column
    >>(2) compare the new and old values - if the values are different, then
    >>that's where the update happened.
    >>
    >>
    >
    >Hello,
    >I would rather second option but I wonder if there is possibility to make it
    >more "elegant"
    >This is sample code ilustrating my idea:
    >
    >DECLARE tname CHAR(40);
    >SET tname = 'tab1'
    >FOR kols AS SELECT colname FROM syscat.columns WHERE tabname = tname
    >DO
    > IF n.kols <> o.kols THEN
    >
    > --do something
    >
    > END IF
    >END FOR
    >
    >Is it possible to make it this way ?
    >
    >Regards
    >Piotr
    >
    >
    Elegance likely isn't waiting for you. But consider the list of columns
    that are unchangeable ... for example the primary key. Can you narrow
    down the subset of fields that require checking? Take those fields that
    are least likely to change, concatenate them into a single string and
    then check the two strings for differences. If they are the same ... no
    need to check the component columns.

    --
    Daniel Morgan
    [url]http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp[/url]
    [url]http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp[/url]
    [email]damorganx.washington.edu[/email]
    (replace 'x' with a 'u' to reply)


    Daniel Morgan Guest

  5. #5

    Default Re: question about trigger

    Daniel Morgan <damorganx.washington.edu> wrote:
    > Piotr wrote:
    >
    >>>Two easy options:
    >>>(1) use separate triggers, one for each column
    >>>(2) compare the new and old values - if the values are different, then
    >>>that's where the update happened.
    >>>
    >>>
    >>
    >>Hello,
    >>I would rather second option but I wonder if there is possibility to make
    >>it more "elegant"
    >>This is sample code ilustrating my idea:
    >>
    >>DECLARE tname CHAR(40);
    >>SET tname = 'tab1'
    >>FOR kols AS SELECT colname FROM syscat.columns WHERE tabname = tname
    >>DO
    >> IF n.kols <> o.kols THEN
    >>
    >> --do something
    >>
    >> END IF
    >>END FOR
    I don't think this would work as you hope. The correlation names "n" and
    "o" refer to columns in the table on which the trigger is defined. If
    there is no column named "kols", you should get a syntax error.
    > Elegance likely isn't waiting for you. But consider the list of columns
    > that are unchangeable ... for example the primary key. Can you narrow
    > down the subset of fields that require checking? Take those fields that
    > are least likely to change, concatenate them into a single string and
    > then check the two strings for differences. If they are the same ... no
    > need to check the component columns.
    Depending on the table structure, this might add more overhead than it would
    be worth.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  6. #6

    Default Re: question about trigger

    Knut Stolze wrote:
    >Daniel Morgan <damorganx.washington.edu> wrote:
    >
    >
    >
    >>Piotr wrote:
    >>
    >>
    >>
    >>>>Two easy options:
    >>>>(1) use separate triggers, one for each column
    >>>>(2) compare the new and old values - if the values are different, then
    >>>>that's where the update happened.
    >>>>
    >>>>
    >>>>
    >>>>
    >>>Hello,
    >>>I would rather second option but I wonder if there is possibility to make
    >>>it more "elegant"
    >>>This is sample code ilustrating my idea:
    >>>
    >>>DECLARE tname CHAR(40);
    >>>SET tname = 'tab1'
    >>>FOR kols AS SELECT colname FROM syscat.columns WHERE tabname = tname
    >>>DO
    >>> IF n.kols <> o.kols THEN
    >>>
    >>> --do something
    >>>
    >>> END IF
    >>>END FOR
    >>>
    >>>
    >
    >I don't think this would work as you hope. The correlation names "n" and
    >"o" refer to columns in the table on which the trigger is defined. If
    >there is no column named "kols", you should get a syntax error.
    >
    >
    >
    >>Elegance likely isn't waiting for you. But consider the list of columns
    >>that are unchangeable ... for example the primary key. Can you narrow
    >>down the subset of fields that require checking? Take those fields that
    >>are least likely to change, concatenate them into a single string and
    >>then check the two strings for differences. If they are the same ... no
    >>need to check the component columns.
    >>
    >>
    >
    >Depending on the table structure, this might add more overhead than it would
    >be worth.
    >
    >
    >
    I don't disagree. But it is just something to consider.

    --
    Daniel Morgan
    [url]http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp[/url]
    [url]http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp[/url]
    [email]damorganx.washington.edu[/email]
    (replace 'x' with a 'u' to reply)


    Daniel Morgan Guest

  7. #7

    Default Re: question about trigger

    > I don't think this would work as you hope. The correlation names "n" and
    > "o" refer to columns in the table on which the trigger is defined. If
    > there is no column named "kols", you should get a syntax error.
    Hello,
    I am new to DB2 and I am trying some options. I was expecting rather
    something like kind of variable defining column name i.e. var = kols. +
    '.tab1' , then if var <> '' then etc...

    Regards
    Piotr


    Piotr Guest

  8. #8

    Default Re: question about trigger

    OK.. that last one lost me. Are you concered about teh way how transition
    variables are defined?
    That's SQL Standard. Out of DB2's control.

    Anyway... I think you are looking for something like an IS UPDATED
    predicate. Or some form of
    Function that takes the transition row and tells you the ordibal of the
    column changed?
    I have seen such requests in the past, but, given that there are ways to do
    the job this feature hasn't reached the top of the to-do stack yet :-(

    Cheers
    Serge


    Serge Rielau Guest

Similar Threads

  1. SQL Server Trigger question
    By ego-adam in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 14th, 01:46 AM
  2. An trigger question
    By ppl in forum Informix
    Replies: 1
    Last Post: November 12th, 05:30 PM
  3. trigger question
    By Newbie in forum Microsoft SQL / MS SQL Server
    Replies: 25
    Last Post: August 26th, 06:13 AM
  4. Trigger question ...
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 04:22 PM
  5. Simple trigger question
    By Kurt in forum Oracle Server
    Replies: 3
    Last Post: January 14th, 02:07 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