Changing an After Insert trigger to Instead of ins

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

Changing an After Insert trigger to Instead of ins

Author

Topic

andy_cuz
Starting Member

1 Post

Posted - 2011-02-02 : 06:15:25

Hi guys I'm very new to triggers and not really dealt much with SPs either but here goes.

I've got a trigger and need to change it to an INSTEAD OF INSERT from and AFTER INSERT trigger but not sure other than changing the words what else I need to do.

Here's the Trigger

ALTER TRIGGER [dbo].[TR_U_Reference_Text]
ON [dbo].[Payment_Table]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Payment_Id BIGINT
SET @Payment_Id = (SELECT MAX(Payment_Id) FROM Payment_Table)

UPDATE PT
SET PT.Reference_Text = CASE WHEN SRT.Reference_Text_IN IS NULL THEN PT.Reference_Text
ELSE SRT.Reference_Text_OUT END,
PT.Agreement_Number = CASE WHEN SRT.Reference_Text_IN IS NULL THEN PT.Agreement_Number
ELSE SRT.Agreement_Number_OUT END,
PT.Short_Surname = CASE WHEN SRT.Reference_Text_IN IS NULL THEN PT.Short_Surname
ELSE SRT.Short_Surname_OUT END,
Status_Code = 'IP'
FROM Payment_Table PT
LEFT OUTER JOIN DBO.SUP_Reference_Text SRT
ON LTRIM(RTRIM(PT.Reference_Text)) = LTRIM(RTRIM(SRT.Reference_Text_IN))
WHERE PT.Payment_Id = @Payment_Id

END

GO

Do I simply change the AFTER to be INSTEAD OF???

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-02 : 06:43:28

Why do you need to change it? What's the desired behaviour afterwards?

With an instead of trigger, the trigger fires instead of the operation being performed. Hence an instead of insert will replace the insert. If you want the insert to still happen, you have to code it.

--
Gail Shaw
SQL Server MVP

jbreslow
Starting Member

16 Posts

Posted - 2011-03-16 : 17:13:11

Hi GilaMonster,

Can you enlighten me and show me how to commit the original changes when using INSTEAD OF? I am trying to use INSTEAD OF in lieu of BEFORE UPDATE (which is not supported).

TIA

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-16 : 17:21:36

besides that it looks right from the beginning, I don't see you referencing inserted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources