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 how to use
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 09/13/2013 :  16:11:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 09/13/2013 :  16:26:45  Show Profile  Reply with Quote
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?
Go to Top of Page

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 09/13/2013 :  16:56:33  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/15/2013 :  04:04:34  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/17/2013 :  11:49:55  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
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
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.06 seconds. Powered By: Snitz Forums 2000