With trigger i wanted to saved system 2/3, 3/3,1/3

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)

With trigger i wanted to saved system 2/3, 3/3,1/3

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2011-12-13 : 16:12:16

Hello,

Is it possible with trigger to saved of system.

In the tbl_table1 are saved in the column Type = 0, 1 or more...

Is it possible if I have 1 in the column Type of first table to saved system Sistem 2/3, Sistem 2/4, 2/5,etc, like this:

tbl_table
Id, Amount
1, 5
2, 10
3, 15

with trigger to saved:

Sistem 2/3
1 * 2
1 * 3
2 * 3

?

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:33:31

sorry not clear, what does 2/3 etc represent? whats your rule for determining them?

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 04:28:25

quote:
Originally posted by visakh16

sorry not clear, what does 2/3 etc represent? whats your rule for determining them?

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

Betting systems. System 2/3, System 3/4, etc. ..

tbl_table
Id, Amount
1, 5
2, 10
3, 15

1*2 = from the first table id1*id2

1*3 = from the first table id1*id3

2*3 = from the first table id2*id3

if i have more rows in the first table must multiply each data to each.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 04:51:02

do you mean this?


select cast(t1.id as varchar(10)) + '*' + cast(t2.id as varchar(10))
from table1 t1
cross join table2 t2
where t1.id <> t2.id

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 05:13:21

quote:
Originally posted by visakh16

do you mean this?

select cast(t1.id as varchar(10)) + '*' + cast(t2.id as varchar(10))
from table1 t1
cross join table2 t2
where t1.id <> t2.id

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

I think so.

Look what I mean:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 05:32:23

ok...lemme know if you need anything else

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 05:35:20

quote:
Originally posted by visakh16

ok...lemme know if you need anything else

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

Hey,

yes, this I mean. Here I need your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 05:50:34

hmm...wat else you're expecting? i thought u told posted suggestion is sufficient

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 06:07:15

quote:
Originally posted by visakh16

hmm...wat else you're expecting? i thought u told posted suggestion is sufficient

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

I meant so:

tbl_table1
Id,IdEvent, Event, Odds
1,1, Event1, 1.50
2,1, Event2, 1.34
3,1, Event3, 1.44

In this case I need to insert the table tbl_table2:

Id, table1EventId, Odds
1, 1, 2.01 (1*2)
2, 1, 2.16 (1*3)
3,1, 1.93 (2*3)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 06:37:17

[code]insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from table1 t1
inner join table1 t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id
[/code]

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 09:44:35

quote:
Originally posted by visakh16

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from table1 t1
inner join table1 t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

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

I tried to use this code:
ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from table1 t1
inner join table1 t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

but no insert in the table2?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 10:15:55

do you mean this?


ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 11:30:44

quote:
Originally posted by visakh16

do you mean this?

ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

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

This code:
insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent

works, but the problem is because t1.odds * t.2odds = 1.50*1.50=2.25
In this case (look print screen) be must 1.50*1.34

This means that each value must be multiplied.

Like this:
1.50*1.34 (1*2) = 2.01
1.50*1.44 (1*3) = 2.16
1.34*1.44 (2*3) = 1.93

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 11:32:57

[code]
ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted i
inner join table1 t1
on t1.IdEvent = i.IdEvent
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id
[/code]

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 11:56:04

quote:
Originally posted by visakh16

ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted i
inner join table1 t1
on t1.IdEvent = i.IdEvent
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

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

and t2.id <> t1.id - if i delete this line works.

returns:
74 1 2.25 - incorrect
75 1 2.01 - correct
76 1 1.80 - incorrect
77 1 2.16 - correct
78 1 1.93 - correct
79 1 2.07 - incorrect

I need returns: 2.01, 2.16, 1.93 .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 12:32:49

[code]
ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted i
inner join table1 t1
on t1.IdEvent = i.IdEvent
inner join table1 t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id
[/code]

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 12:55:24

quote:
Originally posted by visakh16

ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted i
inner join table1 t1
on t1.IdEvent = i.IdEvent
inner join table1 t2
on t2.IdEvent = t1.IdEvent
and t2.id <> t1.id

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

returns - no correct:
94 1 2,25
95 1 2,25
96 1 2,01
97 1 2,01
98 1 1,80
99 1 2,25
100 1 2,01
101 1 2,16
102 1 2,01
103 1 1,80
104 1 1,93
105 1 2,16
106 1 1,93
107 1 2,07

Please create table1 and table2

Structure table1:
ID int Unchecked
IdEvent int Checked
Event nvarchar(50) Checked
Odds numeric(18, 2) Checked

and table2:
ID int Unchecked
table1EventId int Checked
Odds numeric(18, 2) Checked

When I inserted data into table in the table1 should automatically insert data in the table2

I I have data in the table1:
Id IdEvent Event Odds
39 1 ev1 1,50
40 1 ev2 1,34
41 1 ev3 1,44

automatically inserts the data:
Id table1EventId Odds
97 1 2,01 (1.50*1.34)
101 1 2,16 (1.50*1.44)
104 1 1,93 (1.34*144)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 13:08:32

i got the problem


ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from  inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id > t1.id

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 15:45:19

quote:
Originally posted by visakh16

i got the problem

ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id > t1.id

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

Yes but the problem is because returns:
108 1 2,25
109 1 1,80
110 1 2,07

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 18:06:45

quote:
Originally posted by programer

quote:
Originally posted by visakh16

i got the problem

ALTER TRIGGER Trigger4
ON dbo.table1
FOR INSERT /* INSERT, UPDATE, DELETE */
AS

insert table2 (table1EventId,Odds)
select t1.IdEvent, t1.Odds * t2.Odds
from inserted t1
inner join inserted t2
on t2.IdEvent = t1.IdEvent
and t2.id > t1.id

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

Yes but the problem is because returns:
108 1 2,25
109 1 1,80
110 1 2,07

Hello,

Maybe is better understood this:

table1:

Id, Description
1, test1
2, test2
3, test3

table2:

Id, table1Id, Description, RandomInt
10, 1, test1, rnd1
11, 1, test2, rnd2

---------------------------------------------

Id, table1Id, Description, RandomInt
10, 1, test1, rnd2
11, 1, test3, rnd2

--------------------------------------------

Id, table1Id, Description, RandomInt
10, 1, test2, rnd3
11, 1, test3, rnd3

This is example for 3 Doubles system.

Why i need RandomInt? Because I be must combine the data as shown above. I hope I was clear.

Help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 00:15:34

nope...i didnt get what you're explaining. Can you elaborate showing results?

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources