How to use Trigger Before Insert or Update

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

How to use Trigger Before Insert or Update

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-08 : 00:50:52

Dear Freind,

In the following Trigger all values not saved in database. Means i want to fire this Trigger before Insert or Update. Can anybody suggest me what is change in that Trigger.

ALTER TRIGGER AmountForPCTitle
ON dbo.ORDR
AFTER INSERT,
UPDATE
AS

SET NOCOUNT ON

UPDATE x
SET x.DocTotal = COALESCE(i.DocTotalSy, 0) + COALESCE(i.U_Ref, 0) + COALESCE(i.U_Sub, 0)
FROM dbo.ORDR AS x
INNER JOIN inserted AS i ON i.DocEntry = x.DocEntry

Thanks
Harish

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-08 : 03:13:51

I am assuming you want to do something before you update or insert the table.

In the above code, use the For Trigger and add the Insert Statement after your Trigger Update Code.

ALTER TRIGGER AmountForPCTitle
ON dbo.ORDR
FOR INSERT,
UPDATE
AS

SET NOCOUNT ON

Begin

UPDATE x
SET x.DocTotal = COALESCE(i.DocTotalSy, 0) + COALESCE(i.U_Ref, 0) + COALESCE(i.U_Sub, 0)
FROM dbo.ORDR AS x
INNER JOIN inserted AS i ON i.DocEntry = x.DocEntry

<Your Insert goes here>

End

Hope this helps

http://www.sqlserver007.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 03:19:53

What do you mean with "all values not saved in database"?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:46:25

quote:
Originally posted by hspatil31

Dear Freind,

In the following Trigger all values not saved in database. Means i want to fire this Trigger before Insert or Update. Can anybody suggest me what is change in that Trigger.

ALTER TRIGGER AmountForPCTitle
ON dbo.ORDR
INSTEAD OF ~~AFTER~~ INSERT,
UPDATE
AS

SET NOCOUNT ON

UPDATE x
SET x.DocTotal = COALESCE(i.DocTotalSy, 0) + COALESCE(i.U_Ref, 0) + COALESCE(i.U_Sub, 0)
FROM dbo.ORDR AS x
INNER JOIN inserted AS i ON i.DocEntry = x.DocEntry

Thanks
Harish

if you want to fire trigger before insert/update use INSTEAD OF rather than AFTER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources