Null Value In 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

Null Value In Trigger

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-06 : 04:01:01

Dear All,

I have created one trigger. It is working fine.
I want to set null value by default.
Coz when i entered null value that time it is not showing the proper result.
Can anybody suggest me how to define null value in the Trigger.

Thanks and Regards
Harish Patil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 04:03:08

Show us the trigger code.

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

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-06 : 05:54:57

Dear Freind,

I am sending u my Trigger. In that trigger i want to set NULL value on the field U_Ref,U_Sub. While entering suppose i blank that valuse it is given wrong result otherwise sometimes no output. Can u plz tell me how to solve this one ?

ALTER TRIGGER AmountForPC1 ON dbo.ORDR
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

UPDATE dbo.ORDR
SET DocTotal = DocTotalSy + U_Ref + U_Sub
WHERE DocEntry IN ( SELECT DocEntry From Inserted)
END

Thanks and Regards
Harish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 06:00:53

[code]ALTER TRIGGER AmountForPC1
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[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 06:01:45

Beware that your logic above ALWAYS increases the DocTotal.
If old u_ref value is 5 and new value is 7, doctotal is incremented by 7, not the difference which is 2.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources