Trigger with Where Clause

I need a trigger that only looks for changes for a subset of a table.  I'm pretty confident the code below will work across the entire table, but I only want it to record changes when the field FIN_AMT_TYPE_LGCY_CD = 'PHCG' (see final line of code).  There are other values for the field FIN_AMT_TYPE_LGCY_CD such as 'TXCG' and others, but I don't care about changes in those.

Will the code below work or do I need to handle a "Where" condition differently?

Thanks!  

- Bart
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_SHIPPING] on [dbo].[SV_MACORD_FT_SHIP_AMT]
FOR insert, update, delete
as

insert into dbo.Changes_Email (Record_SLOTID, ChangeDate, ChangeType)
select IsNull(i.SLOTID, d.SLOTID), getdate(),
  case when i.SLOTID = d.SLOTID then 'U'
   when i.SLOTID is not null then 'I'
   else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
where FIN_AMT_TYPE_LGCY_CD = 'PHCG'

Open in new window

BartWestphalAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
CORRECTION, posted wrong version, didn't repaste new version:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_SHIPPING] on [dbo].[SV_MACORD_FT_SHIP_AMT]
AFTER insert, update, delete
as

insert into dbo.Changes_Email (Record_SLOTID, ChangeDate, ChangeType)
select IsNull(i.SLOTID, d.SLOTID), getdate(),
  case when i.SLOTID = d.SLOTID then 'U'
   when i.SLOTID is not null then 'I'
   else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
where (i.FIN_AMT_TYPE_LGCY_CD = 'PHCG' or d.FIN_AMT_TYPE_LGCY_CD = 'PHCG')
 and (ISNULL(i.FIN_AMT_TYPE_LGCY_CD, 'z') <> ISNULL(d.FIN_AMT_TYPE_LGCY_CD, 'z'))
0
 
elimesikaCommented:
HI

This will do the work
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_SHIPPING] on [dbo].[SV_MACORD_FT_SHIP_AMT]
FOR insert, update, delete
as

if exists (
select IsNull(i.SLOTID, d.SLOTID), getdate(),
  case when i.SLOTID = d.SLOTID then 'U'
   when i.SLOTID is not null then 'I'
   else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
where FIN_AMT_TYPE_LGCY_CD = 'PHCG'
)
begin
	insert into dbo.Changes_Email (Record_SLOTID, ChangeDate, ChangeType)
	select IsNull(i.SLOTID, d.SLOTID), getdate(),
	  case when i.SLOTID = d.SLOTID then 'U'
	   when i.SLOTID is not null then 'I'
	   else 'D' end
	from inserted i
	full outer join deleted d on i.SLOTID = d.SLOTID
	where FIN_AMT_TYPE_LGCY_CD = 'PHCG'
end

Open in new window

0
 
Scott PletcherSenior DBACommented:
You'll need to qualify the column name in the WHERE, and you have to allow for I or U or D (?) -- so you want to log if a 'PHCG' row is deleted.  Also, presumably you want to log an UPDATE if the value has changed to or from (?) 'PHCG'?

Btw, you should use "AFTER insert, ..." now instead of "FOR insert, ...".


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_SHIPPING] on [dbo].[SV_MACORD_FT_SHIP_AMT]
AFTER insert, update, delete
as

insert into dbo.Changes_Email (Record_SLOTID, ChangeDate, ChangeType)
select IsNull(i.SLOTID, d.SLOTID), getdate(),
  case when i.SLOTID = d.SLOTID then 'U'
   when i.SLOTID is not null then 'I'
   else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
where i.FIN_AMT_TYPE_LGCY_CD = 'PHCG'
 and (ISNULL(i.FIN_AMT_TYPE_LGCY_CD, 'z') <> ISNULL(d.FIN_AMT_TYPE_LGCY_CD, 'z'))
0
 
BartWestphalAuthor Commented:
After testing - it looks like this works great.  Thank you VERY much.  
0
 
Scott PletcherSenior DBACommented:
You're welcome.  Glad it helped!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.