Professional Web Applications Themes

Newbie question - Trigger on Update - Microsoft SQL / MS SQL Server

Hi: I need to write a trigger that inserts a row in a log table when an update occurs. The log table is a generic table with old value, new value and the date of changed. Any help appreciated Thanks ina dvance...

  1. #1

    Default Newbie question - Trigger on Update

    Hi:

    I need to write a trigger that inserts a row in a log
    table when an update occurs.

    The log table is a generic table with old value, new value
    and the date of changed.

    Any help appreciated

    Thanks ina dvance
    Nathan Guest

  2. #2

    Default Re: Newbie question - Trigger on Update

    Nathan wrote: 

    You can download the trial version of OmniAudit and look at what triggers
    and log tables it creates. Or you could buy it to do the work for you.

    www.krell-software.com/omniaudit

    Seriously, if you really just want an example, look at generated triggers
    from any third party auditing tool. There are a few of them around. There
    are things to consider you might not think of such as catching values which
    change from null to not null and vice-versa, BLOB columns cannot be handled
    the same as other columns because the before values are not available in
    standard triggers (unless you go to INSTEAD OF triggers), and so on.

    --
    Steve Troxell
    Krell Software
    Professional Database Development Tools for MS SQL Server
    http://www.krell-software.com



    Steve Guest

  3. #3

    Default Re: Newbie question - Trigger on Update

    step 1:
    Create a table tab1 with the following structure and the
    data as below

    FIELD1
    ------
    a
    b
    c
    x
    y
    z

    step 2:
    Create a table tab1log

    oldvalue newvalue
    -------- --------
    d x
    e y
    f z


    step 3:
    the following is the trigger.

    create trigger tab1Upd on tab1
    for update
    as
    begin
    DECLARE FIELD1 Varchar(1)

    , FIELD1_D Varchar(1)

    if Update(FIELD1)
    begin
    select FIELD1 = FIELD1
    from Inserted

    select FIELD1_D = FIELD1
    from Deleted

    if isnull(FIELD1, '') <> isnull
    (FIELD1_D, '')
    begin
    insert into tab1log( oldvalue,
    newvalue)
    values (FIELD1_D,FIELD1)

    end
    end
    end


    step 4:

    Update tab1 set field1 = 'd' where field1 = 'a'


    step 5:

    select * from tab1log

    oldvalue newvalue
    -------- --------
    d x
    e y
    f z
    a d


    HTH
    Rajesh Peddireddy


     [/ref]
    value 
    >
    >You can download the trial version of OmniAudit and look[/ref]
    at what triggers 
    work for you. 
    generated triggers 
    them around. There 
    catching values which 
    cannot be handled 
    not available in 
    and so on. 
    Rajesh Guest

  4. #4

    Default Re: Newbie question - Trigger on Update

    Thanks a lot.
     [/ref]
    >value 
    >>
    >>You can download the trial version of OmniAudit and look[/ref]
    >at what triggers [/ref]
    the 
    >generated triggers 
    >them around. There 
    >catching values which [/ref]
    columns 
    >not available in [/ref]
    triggers), 
    >.
    >[/ref]
    Nathan Guest

Similar Threads

  1. Allowing update of column only from trigger
    By Shawn Harrison in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 31st, 06:59 PM
  2. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  3. SQL Server Update Trigger
    By Nathan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 08:38 PM
  4. Update trigger problem
    By Stephen F Zelonis in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:52 PM

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