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 2008 Forums
 Transact-SQL (2008)
 SQL query works, trigger not works
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  06:38:22  Show Profile  Reply with Quote
I tried to use SQL this works:

INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event1');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event2');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event3');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event4');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event5');


INSERT INTO tbl_BetSlipSystem (BetSlipEventId)
Select id from
(Select id,row_number() over(order by id) as Number from tbl_BetSlipEvents) as a
where a.Number=1 or a.Number=3

But If I used trigger not works (inserted 5 rows):

ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

Insert into tbl_BetSlipSystem(BetSlipEventId)
Select Id from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number=1


Where is the problem?

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 09/17/2013 :  08:32:48  Show Profile  Reply with Quote
quote:
Originally posted by programer

I tried to use SQL this works:

INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event1');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event2');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event3');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event4');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event5');


INSERT INTO tbl_BetSlipSystem (BetSlipEventId)
Select id from
(Select id,row_number() over(order by id) as Number from tbl_BetSlipEvents) as a
where a.Number=1 or a.Number=3

But If I used trigger not works (inserted 5 rows):

ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

Insert into tbl_BetSlipSystem(BetSlipEventId)
Select Id from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number=1


Where is the problem?

Each of your five inserts causes the trigger to be invoked, and with each invoke it inserts a row into the tbl_BetSlipSystem table.

What do you want it to do?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/17/2013 :  11:48:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src ON src.ID = tgt.BetSlipEventID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					BetSlipEventID
				)
			VALUES	(
					src.ID
				)
WHEN	NOT MATCHED BY	SOURCE
		THEN	DELETE;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 09/17/2013 13:44:02
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  13:20:20  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					BetSlipEventID
				)
			VALUES	(
					src.ID
				)
WHEN	NOT MATCHED BY	SOURCE
		THEN	DELETE;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Thanks for your help.

I got:
Incorrect syntax the keyword WHEN ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/17/2013 :  13:41:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sorry, my bad.
ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src ON src.ID = tgt.BetSlipEventID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					BetSlipEventID
				)
			VALUES	(
					src.ID
				)
WHEN	NOT MATCHED BY	SOURCE
		THEN	DELETE;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 09/17/2013 13:43:19
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  13:42:50  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

Sorry, my bad.
ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src [red]ON src.ID = tgt.BetSlipEventID

WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/17/2013 :  13:45:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See edited response 09/17/2013 : 13:41:22



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  14:06:32  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

See edited response 09/17/2013 : 13:41:22



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



I checked.
The problem is because If I have:
tbl_BetSlipEvents
Id, Event
27, event1
29, event2

and then inserted new values:

31, event4
33, event4
34, event5

Is my tbl_BetSlipEvents:
27, event1
29, event2
31, event3
33, event4
34, event5

SELECT ID
FROM cteSource
WHERE rn IN (1, 3)

WHERE is 1,3. This is ok. But inseted in the second table 27,.

I need to insert 31,33 because my new values is inserted:
31, event3
33, event4
34, event5

Please help.
















Edited by - programer on 09/17/2013 14:08:23
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  14:39:14  Show Profile  Reply with Quote
This I mean:

tbl_BetSlipEvents currently:
Id, Event
27, event1
29, event2

and then I inserted new values:
Id, event
31, event3
33, event4
34, event5

tbl_BetSlipEvents is now:
Id, event
27, event1
29, event2
31, event3
33, event4
34, event5

In a second table the tbl_BetSlipSystem I want to insert:
tbl_BetSlipEvents:
Id, BetSlipEventId
52, 31
53, 34

Pls help













[/quote]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/17/2013 :  14:40:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Only if you insert them at ONCE! In ONE batch.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  14:43:48  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

Only if you insert them at ONCE! In ONE batch.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Yes. How to inserted at once?

I tried to use inserted but inserted three record: SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/17/2013 :  14:51:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  18:41:34  Show Profile  Reply with Quote
Yes I tried and not works.

CREATE TABLE [dbo].[tbl_BetSlipEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_BetSlipEvents] PRIMARY KEY CLUSTERED
(

CREATE TABLE [dbo].[tbl_BetSlipSystem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BetSlipEventId] [int] NULL,
CONSTRAINT [PK_tbl_BetSlipSystem] PRIMARY KEY CLUSTERED



Trigger:
USE [test_events]
GO
/****** Object: Trigger [dbo].[BetSlipEventsTrigger] Script Date: 09/18/2013 00:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BetSlipEventsTrigger]
ON [dbo].[tbl_BetSlipEvents]
AFTER INSERT,
UPDATE,
DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM dbo.tbl_BetSlipEvents
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID
FROM cteSource
WHERE rn IN (1, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;



Insert:
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), ('event3'), ('event4'), ('event5');


I got:
tbl_BetSlipEvents:
ID Event
128 event1
129 event2
130 event3
131 event4
132 event5
133 event1
134 event2
135 event3
136 event4
137 event5



tbl_BetSlipSystem:
64 128
65 130



I need to get:
64 133
65 135

This is because I inserted 5 new values and my row is 1 and 3.

Check and you will see where is my problem.





quote:
Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA


Edited by - programer on 09/17/2013 18:42:24
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/17/2013 :  19:12:05  Show Profile  Reply with Quote
Works now. Inserted was missing

quote:
Originally posted by programer

Yes I tried and not works.

CREATE TABLE [dbo].[tbl_BetSlipEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_BetSlipEvents] PRIMARY KEY CLUSTERED
(

CREATE TABLE [dbo].[tbl_BetSlipSystem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BetSlipEventId] [int] NULL,
CONSTRAINT [PK_tbl_BetSlipSystem] PRIMARY KEY CLUSTERED



Trigger:
USE [test_events]
GO
/****** Object: Trigger [dbo].[BetSlipEventsTrigger] Script Date: 09/18/2013 00:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BetSlipEventsTrigger]
ON [dbo].[tbl_BetSlipEvents]
AFTER INSERT,
UPDATE,
DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM dbo.tbl_BetSlipEvents
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID
FROM cteSource
WHERE rn IN (1, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;



Insert:
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), ('event3'), ('event4'), ('event5');


I got:
tbl_BetSlipEvents:
ID Event
128 event1
129 event2
130 event3
131 event4
132 event5
133 event1
134 event2
135 event3
136 event4
137 event5



tbl_BetSlipSystem:
64 128
65 130



I need to get:
64 133
65 135

This is because I inserted 5 new values and my row is 1 and 3.

Check and you will see where is my problem.





quote:
Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/18/2013 :  14:52:34  Show Profile  Reply with Quote
Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

Trigger:
WITH cteSource(ID,BetSlipDetailId, rn)
AS (
SELECT ID,BetSlipDetailId,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID,
BetSlipDetailId
FROM cteSource
WHERE rn IN (1, 2, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (BetSlipDetailID,
BetSlipEventID,
SystemBet
)
VALUES (
src.BetSlipDetailId,
src.ID,
'1,3'
);

WITH cteSource2(ID,BetSlipDetailId, rn)
AS (
SELECT ID,BetSlipDetailId,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
)
MERGE dbo.tbl_BetSlipSystem AS tgt2
USING (
SELECT ID,
BetSlipDetailId
FROM cteSource2
WHERE rn IN (1, 3)
) AS src2 ON src2.ID = tgt2.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (BetSlipDetailID,
BetSlipEventID,
SystemBet
)
VALUES (
src2.BetSlipDetailId,
src2.ID,
'2,3'
);


quote:
Originally posted by programer

Works now. Inserted was missing

quote:
Originally posted by programer

Yes I tried and not works.

CREATE TABLE [dbo].[tbl_BetSlipEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_BetSlipEvents] PRIMARY KEY CLUSTERED
(

CREATE TABLE [dbo].[tbl_BetSlipSystem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BetSlipEventId] [int] NULL,
CONSTRAINT [PK_tbl_BetSlipSystem] PRIMARY KEY CLUSTERED



Trigger:
USE [test_events]
GO
/****** Object: Trigger [dbo].[BetSlipEventsTrigger] Script Date: 09/18/2013 00:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BetSlipEventsTrigger]
ON [dbo].[tbl_BetSlipEvents]
AFTER INSERT,
UPDATE,
DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM dbo.tbl_BetSlipEvents
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID
FROM cteSource
WHERE rn IN (1, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;



Insert:
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), ('event3'), ('event4'), ('event5');


I got:
tbl_BetSlipEvents:
ID Event
128 event1
129 event2
130 event3
131 event4
132 event5
133 event1
134 event2
135 event3
136 event4
137 event5



tbl_BetSlipSystem:
64 128
65 130



I need to get:
64 133
65 135

This is because I inserted 5 new values and my row is 1 and 3.

Check and you will see where is my problem.





quote:
Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA





Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 09/18/2013 :  15:08:59  Show Profile  Reply with Quote
quote:
Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

How does it not work? Do you get an error, results you did not expect or other?

Can you post your sample data and expected output?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/18/2013 :  15:52:49  Show Profile  Reply with Quote
Not get any error.
Insert only:
WITH cteSource(ID,BetSlipDetailId, rn)
AS (
SELECT ID,BetSlipDetailId,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID,
BetSlipDetailId
FROM cteSource
WHERE rn IN (1, 2, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (BetSlipDetailID,
BetSlipEventID,
SystemBet
)
VALUES (
src.BetSlipDetailId,
src.ID,
'1,3'
);



Code:
WITH cteSource2(ID,BetSlipDetailId, rn)
Like it does not exist.






quote:
Originally posted by Lamprey

quote:
Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

How does it not work? Do you get an error, results you did not expect or other?

Can you post your sample data and expected output?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

programer
Posting Yak Master

Slovenia
217 Posts

Posted - 09/19/2013 :  06:07:35  Show Profile  Reply with Quote
Still I need your help.

quote:
Originally posted by programer

Not get any error.
Insert only:
WITH cteSource(ID,BetSlipDetailId, rn)
AS (
SELECT ID,BetSlipDetailId,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID,
BetSlipDetailId
FROM cteSource
WHERE rn IN (1, 2, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (BetSlipDetailID,
BetSlipEventID,
SystemBet
)
VALUES (
src.BetSlipDetailId,
src.ID,
'1,3'
);



Code:
WITH cteSource2(ID,BetSlipDetailId, rn)
Like it does not exist.






quote:
Originally posted by Lamprey

quote:
Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

How does it not work? Do you get an error, results you did not expect or other?

Can you post your sample data and expected output?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



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.17 seconds. Powered By: Snitz Forums 2000