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 2012 Forums
 Transact-SQL (2012)
 trigger problem

Author  Topic 

programer
Posting Yak Master

221 Posts

Posted - 2013-06-19 : 11:33:14
My tables:

tbl_Slip:
ID Stake Odds Winnings
1 10.00 15.00 0.00
2 10.00 15.00 0.00
3 10.00 15.00 0.00

tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 0
3 2 0

Trigger:
Update tbl_Slip set Winnings = Stake *Odds
from tbl_Slip as s
inner join inserted as i
on i.SlipId = s.Id
where i.IsWinning = 1

I want to calculate Stake*Odds if is in my case
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 1

if is:
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 0

no calculated

So when isWinning is 2x = 1 is calculated. This is for my case.
But if is:
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 1
4 2 1

IsWinning is 3x = 1.

tbl_Slip.Id=tbl_SlipDetails.SlipId

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 11:39:46
sorry your requirement is not clear.

do you mean this?

CREATE TRIGGER Trp_SlipDetails
ON tbl_SlipDetails
FOR INSERT,UPDATE
AS
BEGIN

UPDATE s
set s.Winnings = s.Stake * s.Odds
from tbl_Slip as s
inner join inserted as i
on i.SlipId = s.Id
where i.IsWinning = 1

END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

programer
Posting Yak Master

221 Posts

Posted - 2013-06-19 : 12:01:55
I tried to use your code but not works.

My result is:
2 10,00 15,00 150,00 Calculated IsWinning=1x. This is wrong.

Stake*Odds calculated if are completed all conditions.

In my case are completed all conditions when I updated:
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 0
3 2 0

To:

tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 1

If is
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 0

not calculated because IsWinning = 1 and 0.
Go to Top of Page

programer
Posting Yak Master

221 Posts

Posted - 2013-06-19 : 14:01:47
quote:
Originally posted by programer

I tried to use your code but not works.

My result is:
2 10,00 15,00 150,00 Calculated IsWinning=1x. This is wrong.

Stake*Odds calculated if are completed all conditions.

In my case are completed all conditions when I updated:
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 0
3 2 0

To:

tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 1

If is
tbl_SlipDetails:
ID SlipId IsWinning
1 1 0
2 2 1
3 2 0

not calculated because IsWinning = 1 and 0.




Still I need your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:14:15
Your explanation is still not clear to me. You would be better illustrating with some sample data clearly if my below guess doesnt work

CREATE TRIGGER Trp_SlipDetails
ON tbl_SlipDetails
FOR INSERT,UPDATE
AS
BEGIN

UPDATE s
set s.Winnings = s.Stake * s.Odds
from tbl_Slip as s
inner join (SELECT i1.SlipId
FROM inserted i1
INNER JOIN tbl_SlipDetails sd
ON i1.SlipId = sd.SlipId
GROUP BY i1.SlipId
HAVING SUM(CASE WHEN sd.IsWinning = 0 THEN 1 ELSE 0 END) = 0
)as i
on i.SlipId = s.Id

END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

programer
Posting Yak Master

221 Posts

Posted - 2013-06-20 : 04:12:23
quote:
Originally posted by visakh16

Your explanation is still not clear to me. You would be better illustrating with some sample data clearly if my below guess doesnt work

CREATE TRIGGER Trp_SlipDetails
ON tbl_SlipDetails
FOR INSERT,UPDATE
AS
BEGIN

UPDATE s
set s.Winnings = s.Stake * s.Odds
from tbl_Slip as s
inner join (SELECT i1.SlipId
FROM inserted i1
INNER JOIN tbl_SlipDetails sd
ON i1.SlipId = sd.SlipId
GROUP BY i1.SlipId
HAVING SUM(CASE WHEN sd.IsWinning = 0 THEN 1 ELSE 0 END) = 0
)as i
on i.SlipId = s.Id

END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks thanks works for me!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 04:21:55
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -