Problem with SQL Audit Trigger

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Problem with SQL Audit Trigger

Author

Topic

gustav12
Starting Member

5 Posts

Posted - 2010-02-11 : 10:43:23

hi everybody!!!

i have copied and adapted this sql trigger for my usage, and it works fine in some tables where there is no more than 10 columns, but in other tables it fires the error "There's no PK on table [tableName]"
i've seen the same error on other posts, so maybe there's a solution

Can you help me find the solution please, its driving me crazy!!!
Thanks in advance!!! PD: sorry for my english javascript:insertsmilie('

')

ALTER TRIGGER [dbo].[trg_TriggerName] ON [dbo].[Table]
AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON ;
DECLARE @Bit INT,
@Field INT,
@Char INT,
@Fieldname VARCHAR(128),
@TableName VARCHAR(128),
@PKCols VARCHAR(1000),
@SQL VARCHAR(2000),
@DateTime DATETIME,
@IdUsuario INT,
@CodAuditoria INT

----------------------------------------------------------
-- COLOCAR NOMBRE DE LA TABLA AFECTADA AQUÍ
SET @TableName = 'Table'
----------------------------------------------------------
SET @DateTime = GETDATE()
----------------------------------------------------------
-- UTILIZAR NOMBRE DE LA TABLA AFECTADA AQUÍ (EN SELECT)
SET @IdUsuario = ( SELECT IdUsuMod
FROM dbo.Table
)
----------------------------------------------------------

SET @CodAuditoria = ( SELECT MAX(CodAuditoria)
FROM dbo.Auditoria
)
IF @CodAuditoria IS NULL
SET @CodAuditoria = 1
ELSE
SET @CodAuditoria = @CodAuditoria + 1

SELECT *
INTO #ins
FROM inserted

SELECT *
INTO #del
FROM deleted

-- Get primary key columns for full outer join
-- here is where the error raises..

SELECT @PKCols = COALESCE(@PKCols + ' AND', ' ON') + ' i.'
+ c.COLUMN_NAME + ' = d.' + 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 existe PK (Primary Key) en la tabla %s', 16, -
1, @TableName )
RETURN
END

SELECT @Field = 0
WHILE @Field < ( SELECT MAX(colid)
FROM syscolumns
WHERE id = ( OBJECT_ID(@TableName) )
)
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 @Fieldname = name
FROM syscolumns
WHERE colid = @Field
AND id = OBJECT_ID(@TableName)
SELECT @SQL = 'INSERT INTO Auditoria (TableName, ColumnName, OldValue, NewValue, ModDate, IdUsuario, CodAuditoria)'
SELECT @SQL = @SQL + ' SELECT ''' + @TableName + ''''
SELECT @SQL = @SQL + ',''' + @Fieldname + ''''
SELECT @SQL = @SQL + ',CONVERT(varchar(1000),d.'
+ @Fieldname + ')'
SELECT @SQL = @SQL + ',CONVERT(varchar(1000),i.'
+ @Fieldname + REPLACE('), ''FechaAqui''',
'FechaAqui', @DateTime)

SELECT @SQL = @SQL + REPLACE(', IdUsuario',
'IdUsuario', @IdUsuario)

SELECT @SQL = @SQL + REPLACE(', CodAuditoria',
'CodAuditoria',
@CodAuditoria)

SELECT @SQL = @SQL
+ ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @SQL = @SQL + @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)'

-- exec SQL QUERY
EXEC ( @SQL
)
END

END

END

-- =============================================
-- structure of table 'AUDITORIA'
-- =============================================
-- CREATE TABLE [dbo].[Auditoria](
-- [IdLinea] [int] IDENTITY(1,1) NOT NULL,
-- [TableName] [varchar](50) NULL,
-- [ColumnName] [varchar](50) NULL,
-- [OldValue] [varchar](50) NULL,
-- [NewValue] [varchar](50) NULL,
-- [ModDate] [datetime] NULL,
-- [IdUsuario] [int] NULL,
-- [CodAuditoria] [int] NULL,
-- CONSTRAINT [PK_Auditoria] PRIMARY KEY CLUSTERED
--(
-- [IdLinea] ASC
--)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:46:41

i think that because IF @PKCols IS NULL condition becomes true so it throws the error you're given inside

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

gustav12
Starting Member

5 Posts

Posted - 2010-02-11 : 12:35:49

thanks for the quick reply!
but i don't know why the condition results NULL, because effectively there's a PK on the affected table.. this doesn't happen with other tables, and all of my tables have a PK..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:38:12

add a print(@PKCols) after select and see when its becoming NULL

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

gustav12
Starting Member

5 Posts

Posted - 2010-02-11 : 13:15:44

thanks again!!!
look, i was trying to insert the result query in other table to see it, but it inserts only null values..
is it possible to you to check it in your own tables please?

this is the table "Auditoria"

CREATE TABLE [dbo].[Auditoria](
[IdLinea] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NULL,
[ColumnName] [varchar](50) NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UserId] [nvarchar](50) NULL,
[ModDate] [datetime] NULL,
[AuditType] [int] NULL,
[CodAuditoria] [int] NULL,
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
[IdLinea] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 13:20:21

quote:
Originally posted by gustav12

thanks again!!!
look, i was trying to insert the result query in other table to see it, but it inserts only null values..
is it possible to you to check it in your own tables please?

this is the table "Auditoria"

CREATE TABLE [dbo].[Auditoria](
[IdLinea] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NULL,
[ColumnName] [varchar](50) NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UserId] [nvarchar](50) NULL,
[ModDate] [datetime] NULL,
[AuditType] [int] NULL,
[CodAuditoria] [int] NULL,
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
[IdLinea] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

what do you mean by that?
I dont have your tables nor can i see your system

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

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 13:27:54

Have you made some tests of this type of auditing?

It is very hard to use data from this style of auditing - in particular report on what changed, and when, with respect to other fields.

We used to do it this way, the triggers were huge / slow, and we now only audit whole-rows.

I can provide more information if you are interested.

gustav12
Starting Member

5 Posts

Posted - 2010-02-11 : 13:42:14

hi kristen!!!
your help will be usefull for me!!!
dou you have some particular query to audit changes made to tables??
my idea is to save the changes made to any column of tables, in another table..
i've been working on this query about a week and i'm already tired!!

ii'l be waiting for your help!! thx!!

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 13:49:59

Have a look at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139702#546701

and do please ask any questions (either in that thread, or in this one).

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 13:52:01

... and this thread is useful too

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

gustav12
Starting Member

5 Posts

Posted - 2010-02-12 : 13:11:23

thanks for your help guys!!!! i'v got the solution!!!