March 11, 2010 at 10:27 am
I am working on a versioning control trigger. I have a flag column which is a bit field which allows content to be shown. I will like to be able to audit anything that is set to live = true on insert and update. If Live = false I do not want to add a column to the Audit table. I would like to always add a column to the audit table if it was a AuditType of Deleted.
Here is the bottom of my code: How do I add the if statement for deleted? Thanks for your help.
if(select live from inserted) = 'true'
BEGIN
EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)
SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )
END
March 11, 2010 at 10:54 am
I'd have to see the rest of the trigger to tell you the best way to handle this, but you can detect deleted rows in a trigger because they exist in the "deleted" table and don't exist in the "inserted" table.
Generally speaking, SQL statements work better on Where clauses than on If statements. If you can post the rest of the trigger code, we can probably help turn it into something that will work better.
- 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 11, 2010 at 11:27 am
Hi G Squared, thanks for your prompt response. Here is my entire trigger. Thanks again for your help. I tried some things at the bottom but it is not working.
USE [XXXX]
GO
/****** Object: Trigger [dbo].[Tier1_ChangeTracking] Script Date: 03/11/2010 10:24:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[Tier1_ChangeTracking] on [dbo].[Tier1] for insert, update, delete
as
declare @AuditType char(1), @PKCols VARCHAR(MAX), @sql VARCHAR(MAX)
--Find the Primary keys to be used in the inserted and deleted outer join
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 = 'Tier1'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select * into #ins from inserted
select * into #del from deleted
if exists (select * from inserted)
if exists (select * from deleted)
SET @AuditType = 'U'
else
SET @AuditType = 'I'
else
SET @AuditType = 'D'
--I added this code to prevent multiple inserts by the user trying to get it right
if exists (select * from inserted) and (select live from inserted) = 1
BEGIN
EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)
SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )
END
if exists (select * from deleted)
BEGIN
EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)
SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )
END
March 11, 2010 at 11:43 am
I would probably do it more like this:
ALTER trigger [dbo].[Tier1_ChangeTracking] on [dbo].[Tier1] for insert, update
as
INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content,
BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic,
keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader,
oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent,
olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)
select i.*, d.*, case
when i.T1ID is not null and d.T1ID is not null
then 'U'
else 'I'
end as AuditType
from inserted as i
full outer join deleted as d
on i.T1ID = d.T1ID
where i.live = 1;
go
create trigger dbo.Tier1_ChangeTracking_Del on dbo.Tier1 for delete
as
INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content,
BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic,
keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader,
oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent,
olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)
select i.*, d.*, 'D' as AuditType
from inserted as i
full outer join deleted as d
on i.T1ID = d.T1ID;
You could clean up the second one, because "inserted" is always going to have nulls in that case, so why bother querying it.
I'd also take a look at only auditing the old values. You don't need to record the new values, they're in the Tier1 table already.
Have you taken a look at the articles I wrote last year for this site, on the subject of Audit Trails and Logging? The articles and their discussions cover the subject pretty extensively.
- 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 11, 2010 at 12:22 pm
GSquared, thank you so much for this it is a much better solution. I was using a script which auto generated the triggers and audit tables.
I will read what you recommended. Can you recommend a good script which can auto generate a soultion like yours?
March 11, 2010 at 2:48 pm
George Murphy (3/11/2010)Can you recommend a good script which can auto generate a soultion like yours?
Problem would probably be compatibility ... I'm pretty sure GSquared's solution generator only runs on GSquared platform 😀
Nicely done GSquared
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 12, 2010 at 6:47 am
The one I use is based on the articles I wrote on the subject. It won't work with your audit table structure.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply