Instead Of Trigger Help Needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Instead Of Trigger Help Needed

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-29 : 17:10:08

I am trying to capture the value of a memo field and place it inside a second field before it gets overlayed with the new value. Currently, I am able to capture the old value and populate the second field before the first field gets overlayed but my problem is that the new value that is supposed to overlay the first field does not populate the first field. Any help in getting this done would be greatly appreciated. Here is the SQL trigger that i had written -

CREATE TRIGGER [dbo].[BLGH_TR_Cath_Extension_Dictation]
ON [dbo].[Cath_Extension]
Instead Of Update
AS

BEGIN

SET NOCOUNT ON;

UPDATE Cath_Extension SET Cath_Extension.MD_Dictation_Old = Deleted.MD_Dictation, Cath_Extension.MD_Dictation = Inserted.MD_Dictation
FROM Cath_Extension
inner join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))
And Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
And Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID

END

Go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 17:30:41

Can you show us a data example to make this more clear? Show us what it looks like before the update, what values are being updated, and what it should look like after the trigger fires.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 05:51:14

" WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))"

maybe first 8,000 characters matches?

If using SQL2005 or later change from TEXT to Varchar(MAX) and then you can use a normal AFTER trigger

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 05:52:25

P.S. If your collation on [MD_Dictation] is case INSENSITIVE then you may want to force a Binary Collation on your Not-Equals comparison, above, to catch and case-sensitive changes that users may make.

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-30 : 11:28:25

Before trigger, MD_Dictation_Old is blank. MD_Dictation contains -

Date of Procedure: 02/11/2010
PROCEDURE PERFORMED: Selective bilateral renal arteriography.

After trigger runs, MD_Dictation_Old gets updated with value in MD_Dictation but MD_Dictation does not get updated with the new value -

Date of Procedure: 02/24/2010
PROCEDURE PERFORMED: Cardiac catheterization

So I am able to capture the old value and store it elsewhere but unable to store the new value.

Thanks again for your help.

quote:
Originally posted by tkizer

Can you show us a data example to make this more clear? Show us what it looks like before the update, what values are being updated, and what it should look like after the trigger fires.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-30 : 11:31:20

Hi,

I also tried removing the where clause for "Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))" and just left
the Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
And Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
as the where clause and that also didn't work for me. Thanks for your help.

quote:
Originally posted by Kristen

" WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))"

maybe first 8,000 characters matches?

If using SQL2005 or later change from TEXT to Varchar(MAX) and then you can use a normal AFTER trigger

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 12:25:20

"Before trigger, MD_Dictation_Old is blank."

By "blank" do you mean NULL? Beware that will fail your NOT EQUALS test.

If that column is allowed to be NULL then perhaps:


WHERE (Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))
OR (Cath_Extension.MD_Dictation IS NULL AND Cath_Extension.MD_Dictation_Old IS NOT NULL)
OR (Cath_Extension.MD_Dictation IS NOT NULL AND Cath_Extension.MD_Dictation_Old IS NULL))
...

"I am able to capture the old value and store it elsewhere but unable to store the new value."

I can't see anything wrong with the code ... but !!

I suggest you temporarily change the UPDATE statement to a SELECT so you can see what-is-what - and then maybe do


BEGIN TRANSACTION

UPDATE Cath_Extension 
SET Cath_Extension.MD_Dictation = 'yyy'
FROM Cath_Extension
WHERE Cath_Extension.SS_Event_Cath_ID = some_ID

ROLLBACK

so you can just retry the update repeatedly whilst you do diagnosis and debugging

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-30 : 13:35:24

Sorry for the confusion but MD_Dictation_Old starts out as null but in my last several tests both fields are actually populated with notes so neither one is null nor blank. Thanks again for your help.

quote:
Originally posted by Kristen

"Before trigger, MD_Dictation_Old is blank."

By "blank" do you mean NULL? Beware that will fail your NOT EQUALS test.

If that column is allowed to be NULL then perhaps:

WHERE (Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))
OR (Cath_Extension.MD_Dictation IS NULL AND Cath_Extension.MD_Dictation_Old IS NOT NULL)
OR (Cath_Extension.MD_Dictation IS NOT NULL AND Cath_Extension.MD_Dictation_Old IS NULL))
...

"I am able to capture the old value and store it elsewhere but unable to store the new value."

I can't see anything wrong with the code ... but !!

I suggest you temporarily change the UPDATE statement to a SELECT so you can see what-is-what - and then maybe do

BEGIN TRANSACTION

UPDATE Cath_Extension
SET Cath_Extension.MD_Dictation = 'yyy'
FROM Cath_Extension
WHERE Cath_Extension.SS_Event_Cath_ID = some_ID

ROLLBACK

so you can just retry the update repeatedly whilst you do diagnosis and debugging

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:39:07

if you want to check for NULL and '' use NULLIF(Colum,'') IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 14:12:49

" MD_Dictation_Old starts out as null but in my last several tests both fields are actually populated"

Sounds like you have an "edge condition" which does need to be in your WHERE clause then - but it just happens that the test data, so far, has not exercised that scenario.

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-30 : 14:24:28

Thanks. I'll keep that in mind then. I'm sure I'll be checking for nulls later on after I figure out what's wrong with my code as I am unable to store the new value yet I'm able to capture the old value and store. Any ideas on that? Thanks.

quote:
Originally posted by visakh16

if you want to check for NULL and '' use NULLIF(Colum,'') IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-30 : 14:28:03

Thanks. I understand what you're saying about checking for null values and I will be doing so later on but my main focus right now is why I am not able to store the new value. Old value is captured and stored but not the new value.

quote:
Originally posted by Kristen

" MD_Dictation_Old starts out as null but in my last several tests both fields are actually populated"

Sounds like you have an "edge condition" which does need to be in your WHERE clause then - but it just happens that the test data, so far, has not exercised that scenario.

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 14:52:08

" Any ideas on that?"

Did you try the SELECT (instead of UPDATE) suggestion I made earlier?

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-31 : 10:26:04

I tried to do a select and captured the values of the inserted and deleted in an email. Here is my code -

CREATE TRIGGER [dbo].[BLGH_TR_Cath_Extension_Dictation]
ON [dbo].[Cath_Extension]
Instead Of Update, Insert
AS

Declare @Inserted varchar(8000), @Deleted varchar(8000), @SSEventCathID int, @Notes varchar(8000)

Select @SSEventCathID = Cath_Extension.SS_Event_Cath_ID
From Cath_Extension, Inserted
Where Cath_Extension.SS_Event_Cath_ID = Inserted.SS_Event_Cath_ID

/* For MD_Dictation */
Select @Inserted = Inserted.MD_Dictation, @Deleted = Deleted.MD_Dictation
From Cath_Extension
inner join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))
And Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
And Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID

Set @Notes = 'Deleted: ' + char(13) + char(10) + rtrim(ltrim(@Deleted)) + char(13) + char(10) + 'Inserted: ' + char(13) + char(10) + rtrim(ltrim(@Inserted))

exec msdb.dbo.sp_send_dbmail @profile_name = 'DBMail', @recipients = 'johndoe@email.com',
@copy_recipients = 'johndoe@email.com', @body = @Notes, @Subject = 'Email Values'

I get over 20 emails and I am the only user on our test environment. There are over 50 fields in the table cath_extension. The first email gives me the same notes (deleted) value for both inserted and deleted which is wrong. The second email gives me the correct notes for both deleted and inserted. The rest of the emails again give me just the notes for deleted in both inserted and deleted. First, can you tell me why I received over 20 emails? Second, how come the values in the emails are inconsistent? Thanks

quote:
Originally posted by Kristen

" Any ideas on that?"

Did you try the SELECT (instead of UPDATE) suggestion I made earlier?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:14:50

the above code wont work in case of batch update/inserts as in those cases you will have more than 1 records in deleted,inserted table and since you're trying to store the values in variables, it will hold only a single value and lose the rest. you might want to replace variable with table variable to hold those multiple values in case of a batch operation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-31 : 13:33:32

The code for emailing the values was just a way to look at the values of the deleted and inserted as the trigger runs. I will still be using the original code for what I'm trying to accompish but I still ahve the problem of not being able to store the new value. Any ideas? Thanks

quote:
Originally posted by visakh16

the above code wont work in case of batch update/inserts as in those cases you will have more than 1 records in deleted,inserted table and since you're trying to store the values in variables, it will hold only a single value and lose the rest. you might want to replace variable with table variable to hold those multiple values in case of a batch operation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:47:46

yup. you're doing an inner join with deleted which will cause it to return only updated values. During new inserts, deleted table wont have any values so if you need to capture new values you need to change logic as


..........

From Cath_Extension 
left join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-31 : 14:30:22

Thanks for the reply. I just tried changing the inner join to a left join as you had suggested and now I am able to store the new value BUT now I am unable to capture the old value from MD_Dictation and store it in MD_Dictation_Old. Before I made the change, I was able to capture old value and store but unable to store new value and so now it is the exact opposite. I need to capture both old and new values. Here is my code -

CREATE TRIGGER [dbo].[BLGH_TR_Cath_Extension_Dictation]
ON [dbo].[Cath_Extension]
Instead Of Update
AS

If Update(MD_Dictation)
Begin

Update Cath_Extension Set Cath_Extension.MD_Dictation_Old = Deleted.MD_Dictation, Cath_Extension.MD_Dictation = Inserted.MD_Dictation
From Cath_Extension
left join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
Where Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))
And Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
And Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID

End

Go

quote:
Originally posted by visakh16

yup. you're doing an inner join with deleted which will cause it to return only updated values. During new inserts, deleted table wont have any values so if you need to capture new values you need to change logic as

..........

From Cath_Extension
left join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
WHERE Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:10:53

did you notice diffrence b/w what i posted and what you've. remove the unwanted checks from your where condition which uses inserted and deleted tables and it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-04-01 : 14:01:47

Oops. Sorry I missed those omissions you made. I did try it this time without those checks in my where clause and the results were the same. I am able to store the new value but old value which I am trying to capture is gone. If it helps my cause, typically the record is already in place. This trigger script is meant for an update to this specific. Here is my code -

Update Cath_Extension Set Cath_Extension.MD_Dictation_Old = Deleted.MD_Dictation, Cath_Extension.MD_Dictation = Inserted.MD_Dictation
From Cath_Extension
left join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
Where Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))

Thanks again for your help.

quote:
Originally posted by visakh16

did you notice diffrence b/w what i posted and what you've. remove the unwanted checks from your where condition which uses inserted and deleted tables and it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:04:23

ok. i think this will do trick for you


Update Cath_Extension Set Cath_Extension.MD_Dictation_Old = COALESCE(Deleted.MD_Dictation,Cath_Extension.MD_Dictation_Old), Cath_Extension.MD_Dictation = Inserted.MD_Dictation
From Cath_Extension
left join deleted on Cath_Extension.SS_Event_Cath_ID = deleted.SS_Event_Cath_ID
inner join inserted on Cath_Extension.SS_Event_Cath_ID = inserted.SS_Event_Cath_ID
Where Cast(Cath_Extension.MD_Dictation as varchar(8000))<> Cast(Cath_Extension.MD_Dictation_Old as varchar(8000))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources