Trigger block problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger block problem

Author

Topic

kuku
Starting Member

14 Posts

Posted - 2011-09-18 : 02:46:58

hi everyone..
i have a trigger for audit on my table,but when i wanna update more than 10(just about) rows on my table the trigger blocks the query,
executing does not stop .
why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 03:17:46

can you post your current trigger? without which its difficult to understand why blocking is happening

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kuku
Starting Member

14 Posts

Posted - 2011-09-19 : 00:13:04

CREATE TRIGGER [dbo].[TR_Audit_C_BudgetCategories] ON [dbo].[C_BudgetCategories]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON ;

DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000) ,
@UpdateDate VARCHAR(21) ,
@UserName INT ,
@Type INT ,
@PKSelect VARCHAR(1000) ,
@PK_Constraint_name VARCHAR(100) ,
@PK_Column_Name VARCHAR(100) ,
@count INT ,
@i INT ,
@a VARCHAR(1000) ,
@column VARCHAR(100) ,
@tmpColumn VARCHAR(100) ,
@query VARCHAR(100)
DECLARE @tmp TABLE
(
id INT IDENTITY(1, 1)
NOT NULL ,
cName VARCHAR(100)
)

--Table name
SELECT @TableName = ( SELECT OBJECT_NAME(parent_object_id) AS object_name
FROM sys.objects
WHERE type IN ( 'TR', 'TA' )
AND sys.objects.name = 'TR_Audit_C_BudgetCategories'
)
-- Get primary key select for insert
SET @i = 1
SELECT @PK_Constraint_name = constraint_name
FROM information_schema.table_constraints
WHERE table_name = @TableName
AND constraint_type = 'PRIMARY KEY'

SELECT @PK_Column_Name = column_name
FROM information_schema.constraint_column_usage
WHERE constraint_name = @PK_Constraint_name

SELECT @column = ( SELECT TOP 1
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
)
-- Get list of columns

SELECT *
INTO #ins
FROM INSERTED

SELECT *
INTO #del
FROM DELETED

-- Action
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
BEGIN
SELECT @Type = ( SELECT AuditActionID
FROM C_Audit_Action
WHERE ActionType = 'Update'
)

SET @query = 'SELECT ' + @column + ' FROM #ins'
INSERT INTO @tmp
EXEC ( @query
)

SELECT @UserName = ( SELECT TOP 1
userID
FROM inserted
)
SELECT @PKSelect = 'i.' + @PK_Column_Name
END
ELSE
BEGIN
SELECT @Type = ( SELECT AuditActionID
FROM C_Audit_Action
WHERE ActionType = 'Insert'
)
SET @query = 'SELECT ' + @column + ' FROM #ins'
INSERT INTO @tmp
EXEC ( @query
)
SELECT @UserName = ( SELECT TOP 1
userID
FROM INSERTED
)
SELECT @PKSelect = 'i.' + @PK_Column_Name
END
ELSE
BEGIN
SELECT @Type = ( SELECT AuditActionID
FROM C_Audit_Action
WHERE ActionType = 'Delete'
)
SET @query = 'SELECT ' + @column + ' FROM #del'
INSERT INTO @tmp
EXEC ( @query
)
SELECT @UserName = ( SELECT TOP 1
userID
FROM deleted
)
SELECT @PKSelect = 'd.' + @PK_Column_Name
END

SELECT @count = ( SELECT COUNT(*)
FROM @tmp
)

-- Get primary key columns for full outer join
SET @PKCols = ( SELECT 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 ,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
WHILE @i <= @count
BEGIN
SET @tmpColumn = ( SELECT cName
FROM @tmp
WHERE id = @i
)
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
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
OR @Type = 1
OR @Type = 3
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field

SELECT @sql = 'insert A_ModificationLog (AuditActionID, ModificatedTable, PK_ID, ModificatedColumn, DataBefore, DataAfter, UserID)'
SELECT @sql = @sql + ' select '
+ CAST(@Type AS VARCHAR(10)) + ''
SELECT @sql = @sql + ',''' + @TableName
+ ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname
+ ''''
SELECT @sql = @sql
+ ',convert(varchar(1000),d.'
+ @fieldname + ')'
SELECT @sql = @sql
+ ',convert(varchar(1000),i.'
+ @fieldname + ')'

SELECT @sql = @sql + ','''
+ CAST(@UserName AS VARCHAR(15))
+ ''''

SELECT @sql = @sql
+ ' from #ins i full outer join #del d'
SELECT @sql = @sql + ' on i.' + @PKCols
+ '= d.' + @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)'
SELECT @sql = @sql + ' and ' + @PKSelect
+ '=' + @tmpColumn

EXEC (@sql)

END
SET @i = @i + 1
END
END

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 00:32:47

wow...much more complicated than i thought.
would you mind explaining in words what you've trying to implement with above trigger?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kuku
Starting Member

14 Posts

Posted - 2011-09-19 : 00:51:54

hi, thanks for reply))
i wanna save all changes in C_BudgetCategories table into another A_ModificationLog table,but when i update(or delete) more then 10 rows in C_BudgetCategories the query does not stopped..i have to cancel executing query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 01:01:10

whats the purpose of all those dynamic sql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-19 : 01:05:05

Looks like Nigel Rivett's auditing trigger - all changes column-by-column in a single table.

I have used that style in the past (although without the dynamic SQL) and the Triggers were huge, and the data recorded very hard to report on.

Suggest you audit table-by-table instead. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

kuku
Starting Member

14 Posts

Posted - 2011-09-19 : 01:40:13

visakh16 : i save all changed columns values into variables,then concatenate them using dynamic sql
Kristen : do you know the exact problem why Nigel Rivett's auditing trigger does not work in big sets ??

Kristen
Test

22859 Posts

Posted - 2011-09-19 : 01:52:25

"do you know the exact problem why Nigel Rivett's auditing trigger does not work in big sets ??"

No.

Why do you want to do it that way?

kuku
Starting Member

14 Posts

Posted - 2011-09-19 : 02:18:45

i can't add the audit columns into my tables,i wanna know the history of records

Kristen
Test

22859 Posts

Posted - 2011-09-19 : 02:43:48

You've created a table A_ModificationLog, why can't you create a table, say, A_C_BudgetCategories ?

I can;t help with the dynamic route, as I've said we went down that route, found it wasn't suitable for us, and as a consequence we now do it differently. The details are in the thread I linked to.

kuku
Starting Member

14 Posts

Posted - 2011-09-19 : 08:58:49

i solved the problem,thanks for reply guys

sql2005dba
Starting Member

8 Posts

Posted - 2013-04-10 : 14:53:06

I wish OP would have posted how he solved the problem . I am having a similar issue with a critical system .. Anyone knows??

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 01:18:45

quote:
Originally posted by kuku

i solved the problem,thanks for reply guys

Post the solution so that you can help others who faced similar problem

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:28:02

quote:
Originally posted by sql2005dba

I wish OP would have posted how he solved the problem . I am having a similar issue with a critical system .. Anyone knows??

If you're using SQL 2008 or above you can consider using change data capture

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources