Update Trigger Not firing? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Update Trigger Not firing?

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2010-06-13 : 18:40:10

I'm trying to update data in a table when a column in another table is updated. The problem is that the destination table is not being updated, yet I can simply run a direct update statement against the destination table and it works fine. Below is the syntax of the trigger:

CREATE TRIGGER [dbo].[_DSI_SOP10100DocumentAmount_U]
ON [dbo].[SOP10100DocAmount] FOR UPDATE AS
if UPDATE (docamnt)
BEGIN

declare
@Sales_Doc_Num varchar(21),
@Document_Amount varchar (20)

Select
@Sales_Doc_Num = i.sopnumbe,
@Document_Amount = cast(i.docamnt as varchar (19))
FROM INSERTED I

update a
set a.xUSD_Doc_Amount = @Document_Amount
from inserted i, spxSalesDocument a
where a.Sales_Doc_Num = i.sopnumbe

end

Any ideas why this won't update the destination table when the docamnt is actually being updated? I don't even get an error, just nothing? I tried running a trace against the db but don't see the trigger in their either.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-14 : 01:19:44

quote:
Originally posted by wldodds

Select
@Sales_Doc_Num = i.sopnumbe,
@Document_Amount = cast(i.docamnt as varchar (19))
FROM INSERTED I

How is this going to work if there's more than one row in the inserted table?

First off, rewrite it to handle multiple rows. This should work.

CREATE TRIGGER [dbo].[_DSI_SOP10100DocumentAmount_U]
ON [dbo].[SOP10100DocAmount] FOR UPDATE AS
if UPDATE (docamnt)
BEGIN

update a
set a.xUSD_Doc_Amount = cast(i.docamnt as varchar (19))
from inserted iINNER JOIN spxSalesDocument a ON a.Sales_Doc_Num = i.sopnumbe

END

Are you sure that you're checking updates against the correct table, DB and server? Sure that docamnt is one of the updated columns?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources