Solved

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

Posted on 2003-10-21
11
1,298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9591446
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
ID: 9591925
I concur with scott.
0
 
LVL 1

Author Comment

by:photoz
ID: 9592266
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 18

Accepted Solution

by:
ShogunWade earned 250 total points
ID: 9592315
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
ID: 9592600
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
 
LVL 1

Author Comment

by:photoz
ID: 9592932
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:Scott Pletcher
ID: 9592999
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
ID: 9609111
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:Scott Pletcher
ID: 9609130
But what if the table has more than one column in its primary key?
0
 
LVL 1

Author Comment

by:photoz
ID: 9609708
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
ID: 10826342
Guys,

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

Nice job, photoz!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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