SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger block problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kuku
Starting Member

Armenia
14 Posts

Posted - 09/18/2011 :  02:46:58  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/18/2011 :  03:17:46  Show Profile  Reply with Quote
can you post your current trigger? without which its difficult to understand why blocking is happening

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

Go to Top of Page

kuku
Starting Member

Armenia
14 Posts

Posted - 09/19/2011 :  00:13:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/19/2011 :  00:32:47  Show Profile  Reply with Quote
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/

Go to Top of Page

kuku
Starting Member

Armenia
14 Posts

Posted - 09/19/2011 :  00:51:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/19/2011 :  01:01:10  Show Profile  Reply with Quote
whats the purpose of all those dynamic sql?

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

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/19/2011 :  01:05:05  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/14/2012 08:19:19
Go to Top of Page

kuku
Starting Member

Armenia
14 Posts

Posted - 09/19/2011 :  01:40:13  Show Profile  Reply with Quote
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 ??
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/19/2011 :  01:52:25  Show Profile  Reply with Quote
"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?
Go to Top of Page

kuku
Starting Member

Armenia
14 Posts

Posted - 09/19/2011 :  02:18:45  Show Profile  Reply with Quote
i can't add the audit columns into my tables,i wanna know the history of records
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/19/2011 :  02:43:48  Show Profile  Reply with Quote
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.
Go to Top of Page

kuku
Starting Member

Armenia
14 Posts

Posted - 09/19/2011 :  08:58:49  Show Profile  Reply with Quote
i solved the problem,thanks for reply guys
Go to Top of Page

sql2005dba
Starting Member

8 Posts

Posted - 04/10/2013 :  14:53:06  Show Profile  Reply with Quote
I wish OP would have posted how he solved the problem . I am having a similar issue with a critical system .. Anyone knows??
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/11/2013 :  01:18:45  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/11/2013 :  02:28:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000