my trigger does not update datetime?

Question

my trigger does not update datetime?

thephillgreggan

SSC Veteran

Points: 249
More actions
January 25, 2015 at 8:46 pm

#294734

hi i have written the following trigger to update Update date column UpdtDT on update to any row of ProductsL1 table
alter trigger trigUpdateDateProductsL1 on ProductsL1
after update
as
UPDATE dbo.ProductsL1
SET UpdtDT = GETDATE()
from ProductsL1 p
inner join inserted i on p.ProdID=i.ProdID
go
there were no parse error on trigger but when update a cell on table it does not update datetime instead it displays the error message (shown in attachment). So i have changed to trigger to check weather there an error on trigger and i have changed it to the following
alter trigger trigUpdateDateProductsL1 on ProductsL1
after update
as
UPDATE dbo.ProductsL1
SET UpdtDT = GETDATE()
where ProdID=995
go
so when ever there is an update on table it will change the datetime value in ProdID 995. but even this gave the same error. could any on here knows why? is there a way to fix it?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Don't use the Edit rows window. It's a little twitchy on a good day 🙂

If you put the trigger back as it was and use an update statement to change a row in the Products table, does the trigger fire and update the datetime?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

One more point, the trigger will fail when database is altered to RECURSIVE_TRIGGERS ON. Try this

alter trigger trigUpdateDateProductsL1 on ProductsL1

after update

as

if not UPDATE(UpdtDT)

begin

--print 'updating Data';

UPDATE dbo.ProductsL1

SET UpdtDT = GETDATE()

from ProductsL1 p

inner join inserted i on p.ProdID=i.ProdID

end

else

--print 'updating UpdtDT';

go

Unfoturnately this will not eliminate the problem with Managment Studio edit data tool.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Is ProdId a guaranteed unique value in ProductsL1? The error message indicates that the trigger might be trying to UPDATE more than one row.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 4

isn't there an implied extra join(Cross join?) , because you aliased the table?

--because you aliased the table as p, isn't this an implied extra table gets auto joined?

UPDATE dbo.ProductsL1

SET UpdtDT = GETDATE()

from ProductsL1 p , dbo.ProductsL1 --implied?!

inner join inserted i on p.ProdID=i.ProdID

--the right way i think

UPDATE p

SET UpdtDT = GETDATE()

from ProductsL1 p

inner join inserted i on p.ProdID=i.ProdID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 5

Is there any other trigger created on table ? or unique constraint created on table you are updating?