trigger how to use - SQL Server Forums

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 how to use

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2013-09-13 : 16:11:24

Hello,

I have tbl_BetSlipEvents and tbl_BetSlipSystem

tbl_BetSlipEvents:
Id, Event
127, event1
128, event2
129, event3

tbl_BetSlipSystem
Id, BetSlipEventId
1, 127
2, 128
3, 129

So If I inserted in the tbl_BetSlipEvents I need to insert in the table tbl_BetSlipSystem 127,128,129.

For this I need trigger.

Please help

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 16:26:45

Would be like this, assuming that the Id in tbl_BetSlipSystem is an identity column.

CREATE TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipSystem
AFTER INSERT 

AS

INSERT INTO tbl_BetSlipSystem (BetSlipEventId)
SELECT Id FROM INSERTED;

GO

Do you also need to remove the id's from this table if data is deleted from the tbl_BetSlipEvents table?

programer
Posting Yak Master

221 Posts

Posted - 2013-09-13 : 16:56:33

No that ok.

I forgot for second table.

My second table:
tbl_BetSlipDetails,
Id, Date
182
183

I need to get last record from the tbl_BetSlipDetails and I want to insert in the table tbl_BetSlipSystem:
Id, BetSlipDetailId,
1, 183

I need to use trigger this if is in the tbl_BetSlipDetails type=3

quote:
Originally posted by James K

Would be like this, assuming that the Id in tbl_BetSlipSystem is an identity column.
CREATE TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipSystem
AFTER INSERT

AS

INSERT INTO tbl_BetSlipSystem (BetSlipEventId)
SELECT Id FROM INSERTED;

GO
Do you also need to remove the id's from this table if data is deleted from the tbl_BetSlipEvents table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-15 : 04:04:34

I need to use trigger this if is in the tbl_BetSlipDetails type=3
where is type stored? is it another column in the table?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

[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