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)
 INSERT/DELETE Trigger help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 03/19/2014 :  16:07:35  Show Profile  Reply with Quote
Trigger 1: When a new record is saved in table A insert Field1, Field2, field3, field4, and field5 into table B but only if it does NOT already exist in Table B

Trigger 2: When a record in table A is deleted also delete from table B the row where field1 and field2 are the same between table A & B

I'm struggling with how to do this ...still a SQL novice.

Thank you.

Mike Brown

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/19/2014 :  16:57:16  Show Profile  Reply with Quote
Your insert trigger would be something like this. A similar thing for delete trigger, but you will be using the DELETED virtual table instead of the INSERTED virtual table.
CREATE TRIGGER dbo.OnInsertOnYourTableA
ON TableA 
AFTER INSERT
AS

INSERT INTO TableB
(field1, field2, field3, field4, field5)
SELECT field1, field2, field3, field4, field5
FROM INSERTED i
WHERE NOT EXISTS
(
	SELECT * FROM TableB b
	WHERE i.field1 = b.field1
	AND i.field2 = b.field2
	AND i.field3 = b.field3
	AND i.field4 = b.field4
	AND i.field5 = b.field5
)
GO
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 03/19/2014 :  18:37:37  Show Profile  Reply with Quote
Thank you!!

Mike Brown
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 03/19/2014 :  19:53:08  Show Profile  Reply with Quote
The INSERT trigger works perfectly. I can't get my DELETE trigger to work though ...I'm getting a syntax error.

CREATE TRIGGER dbo.OnInsertOnYourTableA
ON TableA
AFTER DELETE
AS

DELETE FROM TableB
(field1, field2, field3, field4, field5)
SELECT field1, field2, field3, field4, field5
FROM INSERTED i
WHERE EXISTS
(
SELECT * FROM TableB b
WHERE i.field1 = b.field1
AND i.field2 = b.field2
AND i.field3 = b.field3
AND i.field4 = b.field4
AND i.field5 = b.field5
)
GO

Mike Brown
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/20/2014 :  10:48:16  Show Profile  Reply with Quote
Your syntax and logic has several issues. I was showing the insert trigger only as a template for how to write triggers rather than as something to be used for replicating the code as well. Delete trigger should be like this:
CREATE TRIGGER dbo.OnDeleteOnYourTableA
ON TableA 
AFTER DELETE
AS

DELETE b
FROM
	TableB b
	INNER JOIN DELETED d ON 
		d.field1 = b.field1
		AND d.field2 = b.field2
		AND d.field3 = b.field3
		AND d.field4 = b.field4
		AND d.field5 = b.field5;
	
GO
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 03/20/2014 :  11:20:32  Show Profile  Reply with Quote
Thank you James. I appreciate it.

Mike Brown
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/20/2014 :  13:03:36  Show Profile  Reply with Quote
You are very welcome.

The logic that these triggers implement has the following flaw (or may be feature, perhaps). Suppose

1.I insert a row in the TableA with a specific set of values, for example field1 = 1, field2 = 2, field3 = 3, field4 = 4 and field5 = 5. This will insert one row into TableB with exactly those same values.
2. Insert a second row into TableB with exactly those same five values for field1 through field5. Now a second row will be inserted in TableA, but there will still be only one row in TableB.
3. Delete one of the two rows in TableA. Now that will delete one row from TableA, leaving the second row intact, but it will also delete the one row in TableB. This will result in a row in TableA having no corresponding row in TableB.

If this is an issue, you need to redesign the logic in the triggers. If this is the desired behavior, then you are all set.
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 03/20/2014 :  13:22:36  Show Profile  Reply with Quote
Thanks again James. I'll take that into consideration.

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