SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 trigger problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 06/19/2013 :  11:33:14  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/19/2013 :  11:39:46  Show Profile  Reply with Quote
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

Slovenia
217 Posts

Posted - 06/19/2013 :  12:01:55  Show Profile  Reply with Quote
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

Slovenia
217 Posts

Posted - 06/19/2013 :  14:01:47  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/20/2013 :  01:14:15  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/20/2013 01:15:53
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 06/20/2013 :  04:12:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/20/2013 :  04:21:55  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000