Solved

how to access changed columns of deleted/inserted table in trigger

Posted on 2003-10-21
11
1,279 Views
Last Modified: 2008-02-20
Here is what I have so far:

create trigger tr_trigtest on trigtest for update
as
declare @bit int ,
 @field int ,
 @char int,
 @tblname varchar(255),
 @colname varchar(255),
 @oldv nvarchar(255),
 @newv nvarchar(255),
 @sql nvarchar(500)
select @field = 0
--Allow trigger to dynamically figure out its owner table, so I can apply this trigger to multiple tables w/o having to hardcode the table name.
while @field < (select max(colid) from syscolumns where id = (select parent_obj from sysobjects where id=@@PROCID))
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 @tblname=name from sysobjects where id=(select parent_obj from sysobjects where id=@@PROCID)
      select @colname=name from syscolumns where colid = @field and id = (select id from sysobjects where name = @tblname)
    set @sql = N'select convert(varchar(255),'+ @colname + N') from deleted'
    print @sql
    execute @oldv = sp_executesql @sql
    end
end
go

The problem that I am running into is in this line:
    set @sql = N'select convert(varchar(255),'+ @colname + N') from deleted'
When sending this dynamically created line to sp_executesql, the deleted table goes out of scope, so I get an error.

How do you access (dynamically) the column names of the deleted and inserted tables, without having to hardcode.
The deleted and inserted tables go out of scope when using sp_executesql, so I'm stuck.
0
Comment
Question by:photoz
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I have to say I don't think it's a good idea to try to dynamically determine column names in a trigger.  **Way too much** overhead.  

Instead, I suggest creating a SP that will dynamically generate static trigger code.  That is, the SP outputs all the statements needed to create a hard-coded trigger based on the table configuration at the time the SP is run.  Then, if the original table changes, simply re-generate the trigger.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
I concur with scott.
0
 
LVL 1

Author Comment

by:photoz
Comment Utility
Thanks for the idea, but overhead won't be a problem as this trigger is designed to capture manual table edits, not programatic ones.

Anyone else have ideas on how to solve this?
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 250 total points
Comment Utility
If scope is your only problem, then simply

SELECT * into ##temptable
from deleted

etc

will get around the problem.

But I really must say that it seems a very strange thing to do.
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
Your major problem is not in accessing 'the **column names** of the deleted and inserted tables', but in accessing the tables at all.  The inserted and deleted tables scope is only within the trigger, and SQL Statements run by EXEC are outside the trigger's scope.

To accomplish what you are trying to do will require some extremely slow workarounds involving global temp tables, Table variables, and some serious hacks.  I would estimate that speed will be less than 1/100th of the speed a poorly designed trigger would get minimum, and resource usage will be excessive.

Remember:  Your problem will be to get the information from the inserted or deleted tables into a scope that can be used by Dynamic SQL.  No matter what, this is going to be a tricky operation, subject to possible error and locking.

The only possible method that I can see to do this would be:

(1) Create, or ensure that it exists, a Global temp table with a name based on the base table's name, and an additional column for your processes SPID value

(2) Insert the data from the inserted/deleted table into the Global temp table with your SPID value appended <<-- Have not quite worked out the logistics of this yet

(3)  Run your Dynamic SQL against the temp table, including your SPID value to ensure you only work with your data.

(4)  Delete the data from the global temp table

This is so inefficient and ugly that I will not continue the development, since I might become 'infected' :-)

I'm afraid that I agree with Scott et al.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:photoz
Comment Utility
well... I understand everyone's concern for efficiency... I can see where coding a (select * into #X / drop #X) per each row the trigger is called on would be quite a pig. If this trigger was going to be used on production data, I would go the route of the automatic trigger hard-code write... but this trigger is only going to fire on manual edits of a table, normally only on 1 row per fire. So efficiency isn't as critical.

This has been an interesting learning experience... I'm not an SQL writer by nature, hence my lack of efficient practices, but thanks for pointing out the shortcomings of my design...
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I'm sorry if we seemed overly critical.  But in this particular situation, when dealing with a trigger, efficiency is generally *the* primary concern.  For example, often in SPs an extra line or two of code may be used if that makes the code clearer; in a trigger, use additional comments, as many as needed since they aren't in the executable code, instead.
0
 
LVL 1

Author Comment

by:photoz
Comment Utility
Although I accepted an answer for this, it didn't solve my entire issue... here is the code that I wound up using, if someone ever runs into this and needs to see a working example:

drop trigger tr_trigtest
go
create trigger tr_trigtest on trigtest for update
as
declare @bit int ,
      @field int ,
      @char int,
      @tblname      varchar(255),
      @colname varchar(255),
      @ukeyname      varchar(255),
      @ukey      varchar(255),
      @oldv      nvarchar(255),
      @newv      nvarchar(255),
      @user      nvarchar(255),
      @sql      nvarchar(500)
select @field = 0
select * into ##tempdel from deleted
select * into ##tempins from inserted
select @user=user
while @field < (select max(colid) from syscolumns where id = (select parent_obj from sysobjects where id=@@PROCID))
begin
select @field = @field + 1
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
--select @char, @field, @bit  -- debug code to check the bits that are tested.
if (substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0)
      begin
      -- get name of table this trigger is running on.
       select @tblname=name from sysobjects where id=(select parent_obj from sysobjects where id=@@PROCID)
       -- get the name of the column that was changed.
       select @colname=name from syscolumns where colid = @field and id = (select id from sysobjects where name = @tblname)
       -- get the name of the primary key column of the table
      select @ukeyname=name from sysobjects where xtype='PK' and uid = (select id from sysobjects where name = @tblname)

      -- get the value of the primary key for the updated row
       set @sql = N'select @ret=convert(varchar(255),'+ @colname + N') from ##tempdel'
      exec sp_executesql @sql,N'@ret varchar(255) output,@colname varchar(255)',@ukey output,@ukeyname
      -- get the oldvalue of the changed column
       set @sql = N'select @ret=convert(varchar(255),'+ @colname + N') from ##tempdel'
      exec sp_executesql @sql,N'@ret varchar(255) output,@colname varchar(255)',@oldv output,@colname
      select @oldv
      --get the new value of the changed column
       set @sql = N'select @ret=convert(varchar(255),'+ @colname + N') from ##tempins'
      exec sp_executesql @sql,N'@ret varchar(255) output,@colname varchar(255)',@newv output,@colname
      select @newv
      
      --insert everything into generic changelog table.
       set @sql = N'insert changelog values(@tbl,@col,@uk,@old,@new,getdate(),@u)'
      exec sp_executesql @sql,N'@tbl varchar(255),@col varchar(255),@uk varchar(255), @old varchar(255),@new varchar(255), @u varchar(255)',
                                    @tblname,@colname,@ukey,@oldv,@newv,@user
      end
end
drop table ##tempdel
drop table ##tempins
go
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
But what if the table has more than one column in its primary key?
0
 
LVL 1

Author Comment

by:photoz
Comment Utility
this is a simple script. It took me long enough to realize that sp_executesql could have OUTPUT params(not documented in SQL Server books)

I'm not going to delve into multi-column primary key introspection... that could be someone else's question... :-)
0
 

Expert Comment

by:avm_cite
Comment Utility
Guys,

You have to admit that it's a cool trick though.

Nice job, photoz!!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now