Dynamic Audit Trigger
-
I am looking for a way to write a dynamic trigger to insert rows into a transaction log. I can dynamically determine what columns have changed, but I am stuck on getting the old and new values dynamically. I cannot seem to access the inserted or deleted tables correctly.
Does any one have any suggestions?
This is what I have so far:
CREATE TRIGGER trg_U_Employee on Employee FOR UPDATE
AS
BEGIN
declare @colname nvarchar(64)
declare @count integer
declare @flaginsert bit
declare @bit integer
declare @value nvarchar(4000)
declare @oldvalue nvarchar(4000)
set @count = 1
set @bit = 1
set @colcount = 1
declare c1 cursor for select c.name from sysobjects o join syscolumns c on c.id = o.id and o.name = 'Employee' order by colorder
open c1
fetch next from c1 into @colname
while @@fetch_status = 0
begin
if (SUBSTRING(COLUMNS_UPDATED(),@bit,1) & power(2, (@count - 1 )) = power(2, (@count - 1 )))
begin
insert into TransactionLog(transactiontype, transactionsource,foreign_id, columnname, username, value, priorvalue)
select 'U', 'Pipe', inserted.id, @colname, 'Test', @value, @oldvalue
from inserted
end
if @count % cast(8 as int) = 0
begin
set @bit = @bit + 1
set @count = 0
end
set @count = @count + 1
fetch next from c1 into @colname
end
close c1
deallocate c1
end
GO
-
As table name is static, so as column name, you can use case statement to access old, new value for a column depends on column position (column id).
For example, in Pubs.dbo.Employee table
case @colpos when 1 then cast(i.emp_id as varchar)
when 2 then cast(i.fname as varchar)
when 3 then cast(i.minit as varchar)
when 4 then cast(i.lname as varchar)
when 5 then cast(i.job_id as varchar)
when 6 then cast(i.job_lvl as varchar)
when 7 then cast(i.pub_id as varchar)
when 8 then cast(i.hire_date as varchar)
end as newvalue,
case @colpos when 1 then cast(d.emp_id as varchar)
when 2 then cast(d.fname as varchar)
when 3 then cast(d.minit as varchar)
when 4 then cast(d.lname as varchar)
when 5 then cast(d.job_id as varchar)
when 6 then cast(d.job_lvl as varchar)
when 7 then cast(d.pub_id as varchar)
when 8 then cast(d.hire_date as varchar)
end as oldvalue
from inserted i, deleted d where i.emp_id = d.emp_id
-
So, there is no way in SQL server to get a record set and enumerate through the columns to get the column value without specifying the name? I even tried to use executesql but that does not run in the context of a the trigger and when I tried to use select into, to create a temporary table, I get messages about unable to access ntext fields.
If I have to list out the columns explicitly, then why use the updated_columns() function?
-
COLUMNS_UPDATED can be used to detect a multiple column change as a bitmasked value to trigger some actions
* Noel -
COLUMNS_UPDATED
There is a difference between an update to a column and a change to a column. An update simply means that there was an update and that the new value could very well be the same as the old value
If one is using a front-end like VB to invoke the SP, on could beat SQL
and saves the original value, checks it against the updated value and takes the appropriate action..
If not, ( I am sure it can be done... if some thought is put into it..)
Bye...
Where will you be 80 years from now? (So, take it easy!) -
How about:
Select
'Col1' as col_name,
I.col1 as new_val,
D.col1 as old_val
From
inserted I Join
deleted D On
I.primary_key = D.primary_key And
I.col1 <> D.col1
Union
Select
'Col2' as col_name,
I.col2 as new_val,
D.col2 as old_val
From
inserted I Join
deleted D On
I.primary_key = D.primary_key And
I.col2 <> D.col2
I know it would be long select - union combination but it does avoid the cursor which MS recommends against using in triggers. I have never used it but I belieeve it would work. I have typically only wnated to mointor updates to specific columns so I have used If Update(colname) Insert into audit_table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I thought about that and also about nested if statements. The big problem is the number of columns in our tables and the fact that we change the columns quite often. Plus we have over 200 tables that need these triggers and it would be best to have a template method. And we do not want to do this in the application layer because this should be transparent to the many interfaces into our application.
In other databases, I have passed the inserted/deleted tables out into a stored procedure and processed them as recordsets, but I don't think that I can do that in SQL Server.
It may be that I have to use our ERD tool to automatically generate the triggers from the stored table definition. At least that way, I would not have to manually change triggers every time we change columns.
-
It would not be too hard to write script for generating triggers base on table schema info.
-
I'll go also for wz700 suggestion. In fact that's what I use. Trigger script generation based on schema. And it does not matter if you have 500 tables and 200 fields per table that's what scripts are for, right?
* Noel -
I took this code from here somewhere and I modified it. Maybe this will help
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* How To use this Procedure
exec sa_TraceTrigger @TableName = 'apinvc', @Verbose =1 -- To see The Triggers Only
exec sa_TraceTrigger @TableName = 'apinvc', @type = 'U' -- For Update Only
exec sa_TraceTrigger @TableName = 'apinvc', @type = 'IU' -- For insert and Update Only
exec sa_TraceTrigger @TableName = 'apinvc', @type = 'UD' -- For Update and delete Only
exec sa_TraceTrigger @TableName = 'apinvc' -- For insert, Update and delete triggers
*/
if exists (select * from sysobjects where id = object_id('sa_LogTable'))
drop table sa_LogTable
GO
CREATE TABLE sa_LogTable (
LOG_ID bigint IDENTITY (1, 1) NOT NULL ,
LOG_Table varchar (20) NOT NULL ,
LOG_Date datetime NOT NULL Default(CURRENT_TIMESTAMP),
LOG_Action char (1) NOT NULL ,
LOG_VamUser varchar(20) NOT NULL Default('Manual Entry'),
LOG_Keys varchar (200) NULL ,
LOG_Values varchar (7500) NULL ,
LOG_SqlUser varchar(20) NOT NULL Default(CURRENT_USER),
LOG_Application Varchar(100) NOT NULL Default(APP_NAME()),
LOG_HostName Varchar(50) NOT NULL Default(Host_name())
)
GO
if not exists (select * from amwsys..sysindexes where id = object_id('dbo.sa_LogTable') and name = 'sa_LogTable_Log_Table')
CREATE INDEX sa_LogTable_Log_Table ON sa_LogTable (Log_Table)
if not exists (select * from amwsys..sysindexes where id = object_id('dbo.sa_LogTable') and name = 'sa_LogTable_Log_Action')
CREATE INDEX sa_LogTable_Log_Action ON sa_LogTable (Log_Action)
if not exists (select * from amwsys..sysindexes where id = object_id('dbo.sa_LogTable') and name = 'sa_LogTable_Log_Keys')
CREATE INDEX sa_LogTable_Log_Keys ON sa_LogTable (Log_Keys)
GO
if exists (select * from sysobjects where id = object_id('sa_TraceTrigger'))
DROP Procedure sa_TraceTrigger
GO
CREATE Procedure sa_TraceTrigger
@TableName sysname,
@Type char(3) = 'IUD',
@Verbose bit=0,
@fldlist varchar(1000)=''
AS
DECLARE @Body_G varchar(1000), @Body_I varchar(2000), @Body_U1 varchar(4000),
@Body_U2 varchar(4000), @Body_U3 varchar(4000), @Body_U4 varchar(4000), @Body_D varchar(2000),
@EquCond Varchar(2000), @keyCond varchar(2000),
@Cond_U1 varchar(4000), @Cond_U2 varchar(4000), @Cond_U3 varchar(4000) , @Cond_U4 varchar(4000) ,
@Keys varchar(900), @Keyname varchar(60),
@Table sysname, @Schema sysname,
@Column sysname, @DataType Sysname
SELECT @Table =PARSENAME(@TableName,1),
@Schema=ISNULL(PARSENAME(@TableName, 2), CURRENT_USER)
Create table #temp03 (name sysname)
select @Body_G = "insert into #temp03 select name from SysObjects WHERE id = Object_id('dbo."+rtrim(@Table)+"')"
execute (@Body_G)
if not exists (select * from #temp03)
begin
drop table #temp03
return
end
drop table #temp03
SELECT @Body_G=
"IF EXISTS (SELECT * FROM SysObjects WHERE Type = 'TR' AND id = Object_id('"+@Schema+".iTR_Audit_"+@Table+"'))"+char(13)+
" Drop Trigger "+@Schema+".iTR_Audit_"+@Table+char(13)+
"IF EXISTS (SELECT * FROM SysObjects WHERE Type = 'TR' AND id = Object_id('"+@Schema+".uTR_Audit_"+@Table+"'))"+char(13)+
" Drop Trigger "+@Schema+".uTR_Audit_"+@Table+char(13)+
"IF EXISTS (SELECT * FROM SysObjects WHERE Type = 'TR' AND id = Object_id('"+@Schema+".dTR_Audit_"+@Table+"'))"+char(13)+
" Drop Trigger "+@Schema+".dTR_Audit_"+@Table
Select @Body_I ='', @Body_U1 ='', @Body_U2 ='', @Body_U3 ='', @Body_U4 ='', @Body_D ='',
@EquCond ='', @keyCond ='',
@Cond_U1='', @Cond_U2='', @Cond_U3='', @Cond_U4='', @Keys =''
select @Body_I ='Create Trigger iTR_Audit_'+@Table +' ON '+ @TableName +char(13)+
'For Insert ' +char(13)+
'As '+char(13)+
' begin '+char(13)+
' set xact_abort on'+char(13)+
" if (select trigger_nestlevel(object_ID('dbo.iTR_Audit_"+@Table+"'))) > 1 "+char(13)+
' return'+char(13)+
' declare @lcfullname char(30),'+char(13)+
' @lcusername char(20)'+char(13)+
" select @lcusername= 'Manual Entry'"+char(13),
@Body_U1='Create Trigger uTR_Audit_'+@Table +' ON '+@TableName +char(13)+
'For update ' +char(13)+
'As ' +char(13)+
' begin '+char(13)+
' set xact_abort on'+char(13)+
" if (select trigger_nestlevel(object_ID('dbo.uTR_Audit_"+@Table+"'))) > 1 "+char(13)+
' return'+char(13)+
' declare @lcfullname char(30),'+char(13)+
' @lcusername char(20)'+char(13)+
" select @lcusername= 'Manual Entry'"+char(13)+
' Insert into sa_LogTable (LOG_Table, LOG_Keys, LOG_Values, LOG_Action, LOG_VamUser)'+char(13),
@Body_d ='Create Trigger dTR_Audit_'+@Table+' ON '+@TableName +char(13)+
'For delete ' +char(13)+
'As '+char(13)+
' begin '+char(13)+
' set xact_abort on'+char(13)+
" if (select trigger_nestlevel(object_ID('dbo.dTR_Audit_"+@Table+"'))) > 1 "+char(13)+
' return'+char(13)+
' declare @lcfullname char(30),'+char(13)+
' @lcusername char(20)'+char(13)+
" select @lcusername= 'Manual Entry'"+char(13)
Declare FieldList Cursor FOR
Select ', ['+ COL_Name(Object_ID(@TableName), k.COLID)+']' as keyname
from sysIndexKeys k, sysindexes I
Where i.id=object_ID(@TableName) and
i.id = k.id and i.indid=k.indid and
i.status & 0x800 =0x800
order by keyno
Open FieldList
FETCH NEXT FROM FieldList INTO @keyname
WHILE @@FETCH_STATUS=0
BEGIN
Select @Keys= @Keys +@keyname
FETCH NEXT FROM FieldList INTO @keyname
end
CLOSE FieldList
deallocate FieldList
IF LEN(@Keys)>1
select @Keys = SUBSTRING(@Keys, 3, LEN(@Keys)-2)
ELSE
BEGIN
PRINT 'No Primary Keys are available to handle the operation'
RETURN
END
Declare FieldList Cursor FOR
Select COLUMN_NAME, DATA_TYPE
from Information_Schema.Columns
Where TABLE_Name =@Table and
Data_Type not in ('TimeStamp','Text','BINARY', 'varbinary')
Open FieldList
FETCH NEXT FROM FieldList INTO @Column, @DataType
WHILE @@FETCH_STATUS=0
BEGIN
select @Column = rtrim(@Column)
IF CHARINDEX(@Column, @Keys)>0
SELECT @keyCond =@keyCond +
CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN
"isnull(Convert(Varchar(10), I."+ @Column +", 101),'NULL')"
WHEN @DataType in ('bigint','bit', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN
"isnull(Convert(Varchar(16), I."+ @Column +"),'NULL')"
ELSE "isnull(I."+ @Column+",'NULL')" END+' + ',
@EquCond = @EquCond+ ' and I.'+ @Column +' = <A href="mailto
.'+@Column">D.'+@ColumnELSE
if @fldlist = '' OR (@fldlist <> '' and CHARINDEX(@Column, @fldlist) > 0)
begin
if LEN(@Cond_U1) < 2850
SELECT @Cond_U1 =@Cond_U1+char(13)+space(4)+ 'case when I.'+ @Column +' <> <A href="mailto
.'+@Column">D.'+@Column+" then '"+@Column+": '+" + CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN
"isnull(Convert(Varchar(10),D."+ @Column +",101),'NULL')+'=>'+isnull(Convert(Varchar(10),I."+ @Column +",101),'NULL')"
WHEN @DataType in ('bigint','bit', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN
"isnull(Convert(Varchar(16),D."+ @Column +"),'NULL')+'=>'+isnull(Convert(Varchar(16),I."+ @Column +"),'NULL')"
ELSE "isnull(rtrim(D."+ @Column+"),'NULL')+'=>'+isnull(rtrim(I."+ @Column+"),'NULL')" END +"+char(13) else '' end +"
else
if LEN(@Cond_U2) < 3750
SELECT @Cond_U2 =@Cond_U2+char(13)+space(4)+ 'case when I.'+ @Column +' <> <A href="mailto
.'+@Column">D.'+@Column+" then '"+@Column+": '+" + CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN
"isnull(Convert(Varchar(10),D."+ @Column +",101),'NULL')+'=>'+isnull(Convert(Varchar(10),I."+ @Column +",101),'NULL')"
WHEN @DataType in ('bigint','bit', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN
"isnull(Convert(Varchar(16),D."+ @Column +"),'NULL')+'=>'+isnull(Convert(Varchar(16),I."+ @Column +"),'NULL')"
ELSE "isnull(rtrim(D."+ @Column+"),'NULL')+'=>'+isnull(rtrim(I."+ @Column+"),'NULL')" END +"+char(13) else '' end +"
else
if LEN(@Cond_U3) < 3750
SELECT @Cond_U3 =@Cond_U3+char(13)+space(4)+ 'case when I.'+ @Column +' <> <A href="mailto
.'+@Column">D.'+@Column+" then '"+@Column+": '+" + CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN
"isnull(Convert(Varchar(10),D."+ @Column +",101),'NULL')+'=>'+isnull(Convert(Varchar(10),I."+ @Column +",101),'NULL')"
WHEN @DataType in ('bigint','bit', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN
"isnull(Convert(Varchar(16),D."+ @Column +"),'NULL')+'=>'+isnull(Convert(Varchar(16),I."+ @Column +"),'NULL')"
ELSE "isnull(rtrim(D."+ @Column+"),'NULL')+'=>'+isnull(rtrim(I."+ @Column+"),'NULL')" END +"+char(13) else '' end +"
else
SELECT @Cond_U4 =@Cond_U4+char(13)+space(4)+ 'case when I.'+ @Column +' <> <A href="mailto
.'+@Column">D.'+@Column+" then '"+@Column+": '+" + CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN
"isnull(Convert(Varchar(10),D."+ @Column +",101),'NULL')+'=>'+isnull(Convert(Varchar(10),I."+ @Column +",101),'NULL')"
WHEN @DataType in ('bigint','bit', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN
"isnull(Convert(Varchar(16),D."+ @Column +"),'NULL')+'=>'+isnull(Convert(Varchar(16),I."+ @Column +"),'NULL')"
ELSE "isnull(rtrim(D."+ @Column+"),'NULL')+'=>'+isnull(rtrim(I."+ @Column+"),'NULL')" END +"+char(13) else '' end +"
END
FETCH NEXT FROM FieldList INTO @Column, @DataType
END
CLOSE FieldList
deallocate FieldList
IF LEN(@keyCond)>1
SELECT @keyCond= left(@keyCond, LEN(@keyCond)-1)
IF LEN(@Cond_U4)>1
SELECT @Cond_U4= left(@Cond_U4, LEN(@Cond_U4)-23) + " else '' end"
else
IF LEN(@Cond_U3)>1
SELECT @Cond_U3= LEFT(@Cond_U3, LEN(@Cond_U3)-23) + " else '' end"
else
IF LEN(@Cond_U2)>1
SELECT @Cond_U2= LEFT(@Cond_U2, LEN(@Cond_U2)-23) + " else '' end"
else
IF LEN(@Cond_U1)>1
SELECT @Cond_U1= LEFT(@Cond_U1, LEN(@Cond_U1)-23) + " else '' end"
IF LEN(@EquCond)>5
SELECT @EquCond= ' WHERE '+ SUBSTRING(@EquCond, 6, LEN(@EquCond)-5)
select @Body_I = @Body_I + ' if exists (Select * from Inserted)' + char(13)+
' Insert into sa_LogTable (LOG_Table, LOG_Keys, LOG_Action, LOG_VamUser)' + char(13)+
" Select '"+@Table+"', "+@keyCond+",'I',@lcusername from Inserted I " + char(13)+
"end"
select @Body_U1 = @Body_U1 + " Select '"+@Table+"', "+@keyCond+","+@Cond_U1
select @Body_U2 = @Cond_U2
select @Body_U3 = @Cond_U3
SELECT @Body_U4 = @Cond_U4 + char(13)+
SPACE(4)+",'U',@lcusername from Inserted I, Deleted D " + char(13)+
space(4)+@EquCond + char(13)+
"end"
Select @Body_D = @Body_D + ' if exists (Select * from Deleted)' + char(13)+
' Insert into sa_LogTable (LOG_Table, LOG_Keys, LOG_Action, LOG_VamUser) ' + char(13)+
" Select '"+@Table+"', "+@keyCond+",'D',@lcusername from Deleted I " + char(13)+
"end"
If @Verbose=1
BEGIN
Print @Body_G+ char(13)+'GO'
IF CHARINDEX("I", @Type)>0
Print @Body_I + char(13)+'GO'
IF CHARINDEX("U", @Type)>0
Print @Body_U1
Print @Body_U2
Print @Body_U3
Print @Body_U4 + char(13)+'GO'
IF CHARINDEX("D", @Type)>0
Print @Body_D + char(13)+'GO'
END
ELSE
BEGIN
Execute(@Body_G)
IF CHARINDEX("I", @Type)>0
Execute(@Body_I)
IF CHARINDEX("U", @Type)>0
Execute(@Body_U1+@Body_U2+@Body_U3+@Body_U4)
IF CHARINDEX("D", @Type)>0
Execute(@Body_D)
END
GO
-
Somehow I feel that it would be much easy for one-one level log tracking, instead of one-many (sa_LogTable logs all changes) for the following reasons.
1. multiple columns primary key.
2. none-key table.
3.varchar length limitation.
-
What doy ou mean by change the columns quite often? Are you renaming and/or changing the datatype? How many columns in a table? If your tables are that large maybe it is time to look at the design to see if there is a way to normalize the tables. Do you have a lot of Null or empty columns?
Unfortunately you cannot pass table variables as a parameter to a stored procedure, hopefully this will be added in a later version of SQL Server. You could, and I have done this, create temp tables for the inserted and deleted tables (has to be done using select into as Create Table is not allowed in triggers) and then call a stored procedure and reference those tables in an sp for your processing piece, but anything you could do in an sp you can do in a trigger (except for Create Table, etc..:hehe
.Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
We are in the mist of some drastic development changes. I have over 1200 new columns comming in the next month or two and I have been told that there are also a lot of columns moving to different tables.
Actually, you can do a create table in a trigger. I have gotten this piece of code to work for all tables except for those that use NTEXT columns. It is ugly but it does the job. I realize the microsoft does not recommend cursors in triggers, but I feel that if you are careful and watch your resources, you can write more effecient code. Becides what is the difference between writing the cursor in a trigger and writing it in a sproc that the trigger calls?
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trg_U_Employee' AND type = 'TR')
DROP TRIGGER trg_U_Employee
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trg_U_Employee on Employee FOR UPDATE
AS
BEGIN
declare @colname nvarchar(64)
declare @tableid UniqueIdentifier
declare @stmt nvarchar(200)
declare @count integer
declare @colcount integer
declare @bit integer
declare @value nvarchar(4000)
declare @oldvalue nvarchar(4000)
declare @xtype tinyint
declare @tablename nvarchar(64)
set @tablename = 'Employee'
select * into #tempins from inserted
select * into #tempdel from deleted
set @count = 1
set @bit = 1
set @colcount = 1
declare c1 cursor for select c.name, c.xtype
from sysobjects o
join syscolumns c on c.id = o.id and o.name = @tablename
order by colorder
open c1
fetch next from c1 into @colname, @xtype
while @@fetch_status = 0
begin
if ( @colname not in ('id') and @xtype != 99)
begin
if (
SUBSTRING(COLUMNS_UPDATED(),@bit,1) & power(2, (@count - 1 )) = power(2, (@count - 1 ))
)
begin
create table #temp (x nvarchar(4000))
set @stmt = 'insert into #temp select ' + @colname + ' from #tempins'
EXEC sp_executesql @stmt
select @value = x from #temp
set @stmt = 'insert into #temp select ' + @colname + ' from #tempdel'
EXEC sp_executesql @stmt
select @oldvalue = x from #temp
drop table #temp
insert into TransactionLog(transactiontype, transactionsource,foreign_id,columnname, username, value, priorvalue)
select 'U', 'Pipe', inserted.id, @tablename, @colname, 'Test', @value, @oldvalue
from inserted
end
end -- filter
if @count % cast(8 as int) = 0
begin
set @bit = @bit + 1
set @count = 0
end
set @count = @count + 1
set @colcount = @colcount + 1
fetch next from c1 into @colname, @xtype
end
close c1
deallocate c1
drop table #tempins
drop table #tempdel
end
GO
-
there are some problems with that trigger:
1. if more than one row is updated it won't work
(Triggers are fired by statements not by rows )
2. assuming that only 1 row is updated at a time
create table #temp (x nvarchar(4000))
set @stmt = 'insert into #temp select ' + @colname + ' from #tempins'
EXEC sp_executesql @stmt
select @value = x from #temp
set @stmt = 'insert into #temp select ' + @colname + ' from #tempdel'
EXEC sp_executesql @stmt
select @oldvalue = x from #temp
drop table #temp
why that many temp tables? can't you just
set @stmt = 'select @value = cast(' + @colname + ' as varchar(4000)) from inserted'
exec sp_executesql @stmt, '@value varchar(4000) OUTPUT', @value OUTPUT
set @stmt = 'select @oldvalue = cast(' + @colname + ' as varchar(4000)) from deleted'
exec sp_executesql @stmt, '@oldvalue varchar(4000) OUTPUT', @oldvalue OUTPUT
3. Assuming no rows were updated your trigger will still run (Delay overhead)
4. Hopefully you are not on a transaction heavy enviroment
* Noel -
You are right in SQL2000 you can use create table in a trigger. I was working in SQL 7 when I could not use that statement in a trigger.
You are correct that if you are calling an sp that uses a cursor from within a trigger it is that same thing. I have seen, especially in tables with a large # of columns, where the overhead associated with creating the cursor significantly hinders performance regardless of the # of rows selected by the cursor. I have always read and heard that if you can do it without a cursor, don't use a cursor. Obviously there are instances where it has to be done.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply