Solved

Trigger with Where Clause

Posted on 2010-11-10
5
378 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlServer Table Triggers 3 28
SQL Query Syntax error after > 11 39
Split string into 3 separate fields 5 19
What Is an Error? 2 23
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

763 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