Solved

Trigger with Where Clause

Posted on 2010-11-10
5
370 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
  • 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:ScottPletcher
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:
ScottPletcher 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:ScottPletcher
ID: 34122604
You're welcome.  Glad it helped!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I use SQL Stored Procedure to return FedEx Zone 17 41
How does this SELECT query work 11 99
the whoisactive update 12 38
Export import database 4 39
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now