Incorrect syntax near 'row_number', expected 'OVER

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)

Incorrect syntax near 'row_number', expected 'OVER

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2013-09-17 : 04:10:17

ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

Insert into tbl_BetSlipSystem(BetSlipEventId)

Select Id from

(Select row_number() as Number, Id from inserted) as a

Where a.Number=1 or a.Number=3

Why return error:
I used SQL 2008

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2013-09-17 : 05:32:27

tyr this

Select Id from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number=1 or a.Number=3

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

programer
Posting Yak Master

221 Posts

Posted - 2013-09-17 : 06:08:00

Thanks for your help.

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 EMP
where EMP.number=2 and EMP.number=3

My tables:
tbl_BetSlipEvents,
Id, event
75, event1
76, event2
91, event3

tbl_BetSlipSystem
Id, BetSlipEventId

In the table tbl_BetSlipSystem I need to insert 75 and 76 from the table tbl_BetSlipEvents.

My trigger in this moment to insert id=75,76,91.

If I inserted in the table tbl_BetSlipEvents 3 values, will be inserted 3 values: 75,76,91.

I need to insert row 1 and 3, so 75,91 (tbl_BetSlipSystem.Id)

Please help

quote:
Originally posted by programer

ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

Insert into tbl_BetSlipSystem(BetSlipEventId)

Select Id from

(Select row_number() as Number, Id from inserted) as a

Where a.Number=1 or a.Number=3

Why return error:
I used SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 08:36:34

From my vague understanding, it seems like you are expecting a trigger to do something that it is not designed or meant to do.

A trigger gets invoked once for every insert statement. The insert statement may insert one row into your table, or it may insert multiple rows into the table. Still the trigger will be called just once. The INSERTED virtual table that is available to you in the trigger will have the inserted rows.

Given that, you have to work with that to accomplish what you are trying to do. It is not clear to me what you are trying to do, so I am unable to suggest how it is to be done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 11:49:24

[code]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;[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources