Solved

Trigger with Where Clause

Posted on 2010-11-10
5
385 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:BartWestphal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 34105921
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34106959
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 34106965
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
 

Author Closing Comment

by:BartWestphal
ID: 34117206
After testing - it looks like this works great.  Thank you VERY much.  
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34122604
You're welcome.  Glad it helped!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question