Solved

Trigger with Where Clause

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
When are cursors useful? 8 55
TDE for SQL Web Edition 1 29
t-sql left join 2 24
Creating Scalar Function 3 14
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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