INSERT/DELETE Trigger help - 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)

INSERT/DELETE Trigger help

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-03-19 : 16:07:35

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-19 : 16:57:16

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

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-03-19 : 18:37:37

Thank you!!

Mike Brown

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-03-19 : 19:53:08

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-20 : 10:48:16

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

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-03-20 : 11:20:32

Thank you James. I appreciate it.

Mike Brown

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-20 : 13:03:36

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.

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-03-20 : 13:22:36

Thanks again James. I'll take that into consideration.

Mike Brown

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources