Trigger based on condition

[murphyhg]

I am creating a versioning scheme using a trigger and I do not want to insert a new row into the audit table unless the article is live. I have a column called live which is a bit field. So, if LIVE is true I want the row inserted. I tried putting an if statement around the Insert statement but I'm getting this error message back.
Msg 207, Level 16, State 1, Procedure Tier1_ChangeTracking, Line 40
Invalid column name 'Live'.
Msg 207, Level 16, State 1, Procedure Tier1_ChangeTracking, Line 40
Invalid column name 'true'.
Thank you for your help. Here is my code.
USE [GSIGHub]
GO
/****** Object: Trigger [dbo].[Tier1_ChangeTracking] Script Date: 03/11/2010 09:15:40 ******/
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'
IF (Live = 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

 

[SQLSister]

That trigger is a mess. First you do not want to use dynamic SQL in a trigger. Dynaic sql is bad most of the time, it is disatrous in a trigger as you can't test all the possibilites and could get problems in insert. I can see no reason whatso ever to use dynamic sql either,. If you are putting a trigger on a table, you should know what the PK on that table is.

Next what is the purpose of the temp tables? Is it because you are using dynamic sql? Another reason not to use it.

Your if statements make no sense at all. I think you are trying to get whether it is an insert, update or delete but the logic in them doesn't make sense to me. Personally I would write a separate trigger for insert, a spearate one for update and one for delete. Putting them togehter tends to create problems down the road.

"NOTHING is more important in a database than integrity." ESquared

 

[murphyhg]

Thanks for your suggestion. Here is what Gsquared suggested that I do.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, updatedby, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldupdatedby, 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, updatedby, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldupdatedby, 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;