Data Capture Prior to Overwrite

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

SQL Server 2005 Forums

Transact-SQL (2005)

Data Capture Prior to Overwrite

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-04 : 16:38:05

Hi,

Is there a way for me to capture data in a memo field (fielda) and copy it over to another memo field (fieldb) before first memo field get overlayed with new data? I wanted to use the ff. trigger -

quote:

IF UPDATE(MD_Dictation)
BEGIN
UPDATE Cath_Extension
SET Cath_Extension.MD_Dictation_Old = deleted.MD_Dictation
FROM Cath_Extension, deleted, inserted
WHERE Cath_Extension.SS_Event_Cath_ID = Inserted.SS_Event_Cath_ID
END

quote:

But in my set statement, qualifying the md_dictation with deleted wouldn't let me create the trigger. It comes up with an error. Is there a better way for me to capture the data in SQL? ANy help will be greatly appreciated. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-04 : 22:12:21

you can use INSTEAD OF trigger of AFTER trigger

For INSTEAD of trigger, you need to code all the column update process yourself

For AFTER trigger


update c
set    MD_Dictation_Old = d.MD_Dictation
from   Cath_Extension c
       inner join deleted d on c.SS_Event_Cath_ID = d.SS_Event_Cath_ID
where  c.MD_Dictation <> d.MD_Dictation

KH
[spoiler]Time is always against us[/spoiler]

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-05 : 09:38:30

Thanks for the reply. Very much appreciated. I will give it a try and let you know. Thanks

quote:
Originally posted by khtan

you can use INSTEAD OF trigger of AFTER trigger

For INSTEAD of trigger, you need to code all the column update process yourself

For AFTER trigger

update c
set MD_Dictation_Old = d.MD_Dictation
from Cath_Extension c
inner join deleted d on c.SS_Event_Cath_ID = d.SS_Event_Cath_ID
where c.MD_Dictation <> d.MD_Dictation

KH
[spoiler]Time is always against us[/spoiler]

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-03-08 : 10:51:04

Hi,

I tried the ff Instead of trigger but it wouldn't let me capture the old dictation and populate the MD_Dictation_Old field. Here is my trigger -

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 Deleted, Inserted, Cath_Extension
WHERE Deleted.SS_Event_Cath_ID = Cath_Extension.SS_Event_Cath_ID
And Inserted.SS_Event_Cath_ID = Cath_Extension.SS_Event_Cath_ID
AND Cast(Inserted.MD_Dictation as varchar(8000))<> Cast(Deleted.MD_Dictation as varchar(8000))
END

Go

Could you tell me what I am missing? What's happening is that the new dictation gets inserted into MD_Dictation but the MD_Dictation_Old does NOT get overlayed with the old "deleted" dictation. I've also tried it without the last 2 "AND" statements in my where clause but it didn't make any difference to the outcome.

Thanks.

quote:
Originally posted by khtan

you can use INSTEAD OF trigger of AFTER trigger

For INSTEAD of trigger, you need to code all the column update process yourself

For AFTER trigger

update c
set MD_Dictation_Old = d.MD_Dictation
from Cath_Extension c
inner join deleted d on c.SS_Event_Cath_ID = d.SS_Event_Cath_ID
where c.MD_Dictation <> d.MD_Dictation

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources