trigger update - 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)

trigger update

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2012-03-20 : 10:07:26

Hi,
I have a problem with trigger.

In my tbl_BetSlipEvents I update IsWinning=2.
Always to changes back to 1.
Works only 0 or 1. If I update 3,4,5, to changes to 1

My code:

ALTER TRIGGER updateBetSlipEvents ON tbl_BetSlipEvents
AFTER UPDATE
AS

DECLARE @BetSlipDetailId int
DECLARE @Odds float
DECLARE @EventId int
DECLARE @IsWinning bit

SELECT @BetSlipDetailId = BetSlipDetailId, @EventId = EventId, @IsWinning = IsWinning FROM INSERTED

IF EXISTS (SELECT Id FROM tbl_BetSlipEvents WHERE BetSlipDetailId = @BetSlipDetailId AND IsWinning = 0)
BEGIN
UPDATE tbl_BetSlipDetails SET Winnings = 0 WHERE id = @BetSlipDetailId
END
ELSE
BEGIN
SELECT @Odds = exp(sum(log(cast(odds as float)))) FROM tbl_BetSlipEvents WHERE BetSlipDetailId = @BetSlipDetailId AND IsWinning = 1

UPDATE tbl_BetSlipDetails SET Winnings = Stake * @Odds WHERE id = @BetSlipDetailId
END

UPDATE tbl_BetSlipEvents SET IsWinning = @IsWinning WHERE EventId = @EventId AND BetSlipDetailId = @BetSlipDetailId

programer
Posting Yak Master

221 Posts

Posted - 2012-03-20 : 10:11:37

I found mistake. I declared bit instead of int

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:39:15

there's a potential issue with above trigger

you're assuming that INSERTED will be having only one row at a time which is not case always
it can have multiple records in case of batch deletions so you want be able to store values in variables like above. You should be joining to inserted table in your update statements in that case for required update logic

something like


UPDATE bsd
SET bsd.Winnings = 0 
FROM tbl_BetSlipDetails bsd
INNER JOIN INSERTED i
ON  bsd.id = i.BetSlipDetailId
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources