Join inside trigger? - SQL Server Forums

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

Join inside trigger?

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2011-03-08 : 15:54:07

I am trying to create a Trigger, that will update the History Table with Receipt Information.

My Source Table has a SourceID and a ReceiptID.

So when i Update Receipt table, i want to copy that record to the history table, but i first need to get the SourceID of the source that is being updated.

this is what i have now:

ALTER TRIGGER [dbo].[trg_ReceiptHistory]
ON [dbo].[tblReceipt]
for UPDATE
AS
begin try
INSERT INTO tblHistorySource(RecShipName, RecShipAddress, sourceID)
select t1.shippingName, t1.shippingAddress, t2.sourceID
from [DELETED] t1, tblSource t2
where t2.receiptID = t1.receiptID
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
RAISERROR('Error in Source Hisotry Trigger' ,16,1)
ROLLBACK TRAN
END CATCH
when i try to do a simple update to the tblReceipt, i get the following error:

Msg 50000, Level 16, State 1, Procedure trg_ReceiptHistory, Line 24
Error in Source Hisotry Trigger
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Am I doing it wrong? please help :(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-08 : 16:05:30

Seems like your error is coming from the trigger on tblHistorySource.

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

Subscribe to my blog

xrum
Yak Posting Veteran

87 Posts

Posted - 2011-03-08 : 16:07:07

thanks, i got it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources