Professional Web Applications Themes

Dynamically set the column name for IF UPDATE ( column ) in trigger - Microsoft SQL / MS SQL Server

Sql Server 2000 Update Trigger Question Is there any way to dynamically set the column name when using the [ { IF UPDATE ( column ) ] trigger conditional statement. I have tried creating the statement as a string and executing it and that did not work. example: {set sql = 'If update(' + columnName + ')' Find attached a copy of my trigger code: ALTER trigger tr_trigtest on trigtest for update as declare bit int , field int , char int, SQLquery nvarchar(500), fieldName varchar(500) select field = 0 while field < (select max(colid) from syscolumns where id = ...

  1. #1

    Default Dynamically set the column name for IF UPDATE ( column ) in trigger

    Sql Server 2000 Update Trigger Question

    Is there any way to dynamically set the column name when
    using the [ { IF UPDATE ( column ) ] trigger conditional
    statement.

    I have tried creating the statement as a string and
    executing it and that did not work.

    example: {set sql = 'If update(' + columnName + ')'

    Find attached a copy of my trigger code:

    ALTER trigger tr_trigtest on trigtest for update
    as
    declare bit int ,
    field int ,
    char int,
    SQLquery nvarchar(500),
    fieldName varchar(500)
    select field = 0
    while field < (select max(colid) from syscolumns where id
    = (select id from sysobjects where name = 'trigtest'))
    begin
    select field = field + 1
    SELECT fieldName = (select name from syscolumns
    where colid =field and id = (select id from sysobjects
    where name = 'trigtest'))

    set SQLquery = 'if update('+fieldName+')
    select ('+fieldName+')'
    exec sp_executesql SQLquery

    end

    When the trigger runs I receive the following error
    message:

    Server: Msg 140, Level 15, State 1, Line 1
    Can only use IF UPDATE within a CREATE TRIGGER statement.


    I'm using the following version of SQL:
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600:
    Service Pack 1)

    Thank you.


    Jay Guest

  2. #2

    Default Re: Dynamically set the column name for IF UPDATE ( column ) in trigger

    Can you explain what you are trying to do here? If more than one column
    needs to be checked inside the trigger, you can use sequential IF UPDATE()
    clauses or use COLUMNS_UPDATED() clause which allows you to check multiple
    columns using bitmasks.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Dynamically set the column name for IF UPDATE ( column ) in trigger

    The code I provided dynamically returns all columns within
    a given table and then loops through them to find which
    field was updated. I'm trying to avoid hard coding field
    names.

    The purpose of this code is to log field level changes.

    COLUMNS_UPDATED() works however it returns all fields even
    though a given field value was not changed:
    Ex: assume the i51 contains value '222'
    If you issue the following command:
    update test set i51='222'
    COLUMNS_UPDATED() will indicate that i51 has changed even
    though the content has not changed.


    Thank you for your help,
    Jay
     
    than one column 
    sequential IF UPDATE() 
    to check multiple 
    Jay Guest

  4. #4

    Default Re: Dynamically set the column name for IF UPDATE ( column ) in trigger

    I am not sure how you have tried to use the COLUMNS_UPDATED() clause. Each
    byte in the COLUMNS_UPDATED() encodes 8 columns and you can use SUBSTRING or
    PATINDEX function in a CASE expression or a WHILE loop to get each byte
    value.

    DECLARE ColID INT
    DECLARE Cols VARCHAR(8000)
    SET Cols = SPACE(0)
    SET ColID = 1
    WHILE ColID <= (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = tbl)
    BEGIN
    IF (SUBSTRING(COLUMNS_UPDATED(),(ColID - 1) / 8 + 1, 1)) &
    POWER(2, (ColID - 1) % 8) =
    POWER(2, (ColID - 1) % 8)
    SET Cols = Cols + CAST(ColID AS VARCHAR) + ','
    SET ColID = ColID + 1
    END
    PRINT 'Updated columns are :' + Cols

    Add similar logic into the trigger & you can track the multiple column
    updates upto 1024 columns. You can find more details about this clause in
    SQL Server Books Online.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  5. #5

    Default Re: Dynamically set the column name for IF UPDATE ( column ) in trigger

    Jay (com) writes: 

    You are doing some auditing I guess? Well, bad news for you. The update
    may be to set the column to the same value.

    If you are going to roll your own, keep it simple, and log only before
    and after values of full rows.

    Or check out www.lumigent.com or www.redmatrix.com for established
    third-party solutions.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: Dynamically set the column name for IF UPDATE ( column ) in trigger

    Thank you for all you help.

    I found a terrific script that does all the logging/audit
    trails . Find attached a link to the script

    http://www.developersdex.com/gurus/code/498.asp

    and the actual code:

    /*
    This trigger audit trails all changes made to a table.
    It will place in the table Audit all inserted, deleted,
    changed columns in the table on which it is placed.
    It will put out an error message if there is no primary
    key on the table
    */

    --Set up the tables
    create table Audit (TableName varchar(128), FieldName
    varchar(128), OldValue varchar(1000), NewValue varchar
    (1000))
    go
    create table trigtest (i int not null, j int not null, s
    varchar(10), t varchar(10))
    go
    alter table trigtest add constraint pk primary key (i, j)
    go

    create trigger tr_trigtest on trigtest for insert, update,
    delete
    as

    declare bit int ,
    field int ,
    char int ,
    fieldname varchar(128) ,
    TableName varchar(128) ,
    PKCols varchar(1000) ,
    sql varchar(2000)

    select TableName = 'trigtest'

    select * into #ins from inserted
    select * into #del from deleted

    -- Get primary key columns for full outer join
    select PKCols = coalesce(PKCols + ' and', ' on') + '
    i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    if PKCols is null
    begin
    raiserror('no PK on table %s', 16, -1, TableName)
    return
    end

    select field = 0
    while field < (select max(colid) from syscolumns where
    id = (object_id('trigtest')))
    begin
    select field = field + 1
    select bit = (field - 1 )% 8 + 1
    select bit = power(2,bit - 1)
    select char = ((field - 1) / 8) + 1
    if substring(COLUMNS_UPDATED(),char, 1) & bit > 0
    begin
    select fieldname = name from syscolumns where colid =
    field and id = object_id('trigtest')
    select sql = 'insert Audit (TableName, FieldName,
    OldValue, NewValue)'
    select sql = sql + ' select ''' + TableName + ''''
    select sql = sql + ',''' + fieldname + ''''
    select sql = sql + ',convert(varchar(1000),d.' +
    fieldname + ')'
    select sql = sql + ',convert(varchar(1000),i.' +
    fieldname + ')'
    select sql = sql + ' from #ins i full outer join
    #del d'
    select sql = sql + PKCols
    select sql = sql + ' where i.' + fieldname + ' <>
    d.' + fieldname
    select sql = sql + ' or (i.' + fieldname + ' is
    null and d.' + fieldname + ' is not null)'
    select sql = sql + ' or (i.' + fieldname + ' is not
    null and d.' + fieldname + ' is null)'

    exec (sql)
    end
    end
    go


    insert trigtest select 1,1,'hi', 'bye'
    insert trigtest select 2,2,'hi', 'bye'
    insert trigtest select 3,3,'hi', 'bye'
    update trigtest set s = 'hibye' where i <> 1
    update trigtest set s = 'bye' where i = 1
    update trigtest set s = 'bye' where i = 1
    update trigtest set t = 'hi' where i = 1
    select * from Audit
    select * from trigtest

    go
    drop table Audit
    go
    drop table trigtest
    go

     [/ref]
    within [/ref]
    field 
    >
    >You are doing some auditing I guess? Well, bad news for[/ref]
    you. The update 
    log only before 
    established 
    s.asp 
    Jay Guest

Similar Threads

  1. Question create column in table dynamically
    By saidujjama in forum Brainstorming Area
    Replies: 0
    Last Post: May 11th, 09:05 AM
  2. Allowing update of column only from trigger
    By Shawn Harrison in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 31st, 06:59 PM
  3. Dynamically change column widths
    By Iain Carlin in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 15th, 02:31 AM
  4. Replies: 1
    Last Post: August 16th, 10:16 PM
  5. Have trigger supply value for NOT NULL column on insert
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 02:59 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