March 31, 2009 at 9:12 am
I have tables with records when updated or delete would be moved to audit tables. But, I dont want to audit few columns in my tables so how should I do it inside the trigger.
Create table abc (col1 int primary key, col2 int, col3 varchar(200), col4 varchar(15), col5 datetime)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create trigger [dbo].[abc_Tra1]
on [dbo].[abc]
after update,
delete
as
begin
set nocount on
set xact_abort on
set arithabort on
if (TRIGGER_NESTLEVEL()<2)
BEGIN
insert dbo.audit_abc
(col1,col2,col2,col4,col5,audittype)
select d.col1,d.col2,d.col2,d.col4,d.col5,
case
when i.col1 is null then 'delete'
else 'update'
end
from deleted d
left outer join inserted i
on d.col1 = i.col1
end
end
Here, I dont want to audit when updates are performed to col2 & col3 & whenever updates are performed on rest of the columns the old records should be pushed to audit tables.
I'm using after update, delete trigger on the table to capture the audit records.
March 31, 2009 at 9:29 am
Go to Books Online (BOL) and lookup COLUMNS_UPDATED and UPDATE functions. That is where I'd start. let us know if you have any additional questions after reading about these functions.
March 31, 2009 at 12:30 pm
COLUMNS_UPDATED is not helpful. It requires lot of validation which would impact the performance. I want this to be validated at the query level.
if all columns except my two columns are not updated, then do audit.
If I use COLUMNS_UPDATED the condition would be validated every time there is some action on the table instead I want the query to insert record in audit table except for my two columns updated action. I think some where condition to the query would do this.
March 31, 2009 at 12:44 pm
Putting something in the Where clause will take more processing power than using the updated columns function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2009 at 1:07 pm
To clarify, if col2 and col3 are updated, don't audit regardless if any other columns are updated? Or, if only col2 and/or col3 are updated don't audit? Sorry, your requirements are all that clear.
March 31, 2009 at 1:12 pm
For example, take my query:
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)
select d.col1,d.col2,d.col2,d.col4,d.col5,
case
when i.col1 is null then 'delete'
else 'update'
end
from deleted d
left outer join inserted i
on d.col1 = i.col1
where col2<>col2
and col5<>col5
In this query except col3 & col5 all other columns would be audited. As col1 is the key field it would not take much performance impact.
moreover, (using COLUMN_UPDATES) the columns cordinal position if it has any design issue in the future.
March 31, 2009 at 2:15 pm
RJ (3/31/2009)
For example, take my query:
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)select d.col1,d.col2,d.col2,d.col4,d.col5,
case
when i.col1 is null then 'delete'
else 'update'
end
from deleted d
left outer join inserted i
on d.col1 = i.col1
where col2<>col2
and col5<>col5
In this query except col3 & col5 all other columns would be audited. As col1 is the key field it would not take much performance impact.
moreover, (using COLUMN_UPDATES) the columns cordinal position if it has any design issue in the future.
First, this doesn't clarify the requirements for me at all.
Second, assuming the above is fired in a triggered fired for either an update or delete, it isn't going to work. One, it has ambiguous column names in the WHERE clause. Two, when fired for a delete, the WHERE clause, if properly aliased, will always be false as <> null will always return a false. You would need to check for null values on the RIGHT side of the LEFT OUTER JOIN.
March 31, 2009 at 2:18 pm
You can use the Update() function for this, if you prefer column names over ordinal positions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2009 at 2:53 pm
1. My Requirement is I have 26 columns in a table & we might add few more columns. Out of 26, two columns one column is in 6 position & other is in 15th position which I dont want to audit. All other columns I want to audit.
2. In my query that I posted I missed to put alias name in where clause. so the query would be:
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)
select d.col1,d.col2,d.col2,d.col4,d.col5,
case
when i.col1 is null then 'delete'
else 'update'
end
from deleted d
left outer join inserted i
on d.col1 = i.col1
where d.col2<>i.col2
and d.col5<>i.col5
3. yes, during delete you will not see any records for inserted which will fail. I wanted to use any methods that simply help me in this state as my system is high performance application & cann't take any complex functions. More over whether Updated_COLUMNS would work if i have more than 32 columns in my table (also cardinal position scare me). I'm still not clear how to implement my requirement.
March 31, 2009 at 3:02 pm
Does this give you what you want? Realize that this is going to be a large insert query for your actual table.
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)
select
d.col1,
d.col2,
d.col2,
d.col4,
d.col5,
case
when i.col1 is null
then 'delete'
else 'update'
end
from
deleted d
left outer join inserted i
on d.col1 = i.col1
where
((d.col1 <> i.col1) or (i.col1 is null))
or ((d.col4 <> i.col4) or (i.col4 is null))
or ((d.col5 <> i.col5) or (i.col5 is null))
-- If changes only occurred to col2 or col3, no insert will occur.
March 31, 2009 at 3:48 pm
this is a good query but would fail if there are NULL values in deleted. i think the below query would fix it. But, anyway I have to double check if this is better than any other methods. please also let me know how to implement this using "UPDATE" function inside the trigger in order to mention the column name explicitly.
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)
select
d.col1,
d.col2,
d.col2,
d.col4,
d.col5,
case
when i.col1 is null
then 'delete'
else 'update'
end
from
deleted d
left outer join inserted i
on d.col1 = i.col1
where
((d.col1 <> i.col1) or (i.col1 is null) or (d.col1 is null))
or ((d.col4 <> i.col4) or (i.col4 is null) or (d.col4 is null))
or ((d.col5 <> i.col5) or (i.col5 is null) or (d.col5 is null))
--- If changes only occurred to col2 or col3, no insert will occur.
March 31, 2009 at 3:51 pm
RJ (3/31/2009)
this is a good query but would fail if there are NULL values in deleted. i think the below query would fix it. But, anyway I have to double check if this is better than any other methods. please also let me know how to implement this using "UPDATE" function inside the trigger in order to mention the column name explicitly.
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)select
d.col1,
d.col2,
d.col2,
d.col4,
d.col5,
case
when i.col1 is null
then 'delete'
else 'update'
end
from
deleted d
left outer join inserted i
on d.col1 = i.col1
where
((d.col1 <> i.col1) or (i.col1 is null) or (d.col1 is null))
or ((d.col4 <> i.col4) or (i.col4 is null) or (d.col4 is null))
or ((d.col5 <> i.col5) or (i.col5 is null) or (d.col5 is null))
--- If changes only occurred to col2 or col3, no insert will occur.
That depends on your column definitions. If the columns do not allow null values, you only have to worry about the columns in the inserted table being null when a delete is done due to the left outer join.
March 31, 2009 at 4:10 pm
Yes. Other than the key column we wouldn't need to add "or ((d.col5 <> i.col5) or (i.col5 is null) or (d.col5 is null))" to a column that is not nulllable as it will never have a null value.
instead adding "or ((d.col5 <> i.col5))" would be good enough.
March 31, 2009 at 4:18 pm
RJ (3/31/2009)
Yes. Other than the key column we wouldn't need to add "or ((d.col5 <> i.col5) or (i.col5 is null) or (d.col5 is null))" to a column that is not nulllable as it will never have a null value.instead adding "or ((d.col5 <> i.col5))" would be good enough.
You need to check for null values in the insert statement DUE TO THE LEFT OUTER JOIN. That is why you need the following code, even IF all columns are not nullable:
insert dbo.audit_abc (col1,col2,col3,col4,col5,audittype)
select
d.col1,
d.col2,
d.col2,
d.col4,
d.col5,
case
when i.col1 is null
then 'delete'
else 'update'
end
from
deleted d
left outer join inserted i
on d.col1 = i.col1
where
((d.col1 <> i.col1) or (i.col1 is null))
or ((d.col4 <> i.col4) or (i.col4 is null))
or ((d.col5 <> i.col5) or (i.col5 is null))
-- If changes only occurred to col2 or col3, no insert will occur.
On a delete, there will be NO records on the inserted side of the join.
March 31, 2009 at 5:17 pm
the below one just worked fine. I have to test the performance alone now.
col1 - primary key
col2 & col3 - Need not audit
col4 - nullable column
col5 - not nullable column
select
d.col1,
d.col2,
d.col3,
d.col4,
d.col5,
case
when i.col1 is null
then 'delete'
else 'update'
end
from
deleted d
left outer join inserted i
on d.col1 = i.col1
where
(
i.col1 is null
or ((d.col4 <> i.col4)
or (i.col4 is not null and d.col4 is null)
or (i.col4 is null and d.col4 is not null))
or d.col5 <> i.col5
)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply