I'm trying to create a basic database trigger that conditionally deletes rows from database1.table1 when a row from database2.table2 is deleted. I'm new to triggers and was hoping to learn the best way to accomplish this. This is what I have so far. Suggestions?
CREATE TRIGGER sampleTrigger
ON database1.dbo.table1
FOR DELETE
AS
IF EXISTS (SELECT foo
FROM database2.dbo.table2
WHERE id = deleted.id
AND bar = 4)
-- If there is a row that exists in database2.dbo.table2
-- matching the id of the deleted row and bar=4, delete
-- it as well.
-- DELETE STATEMENT?
GO
This question is related to
sql-server
sql-server-2005
triggers
INSERTED
and DELETED
are virtual tables. They need to be used in a FROM
clause.
CREATE TRIGGER sampleTrigger
ON database1.dbo.table1
FOR DELETE
AS
IF EXISTS (SELECT foo
FROM database2.dbo.table2
WHERE id IN (SELECT deleted.id FROM deleted)
AND bar = 4)
Better to use:
DELETE tbl FROM tbl INNER JOIN deleted ON tbl.key=deleted.key
I would suggest the use of exists
instead of in
because in some scenarios that implies null values the behavior is different, so
CREATE TRIGGER sampleTrigger
ON database1.dbo.table1
FOR DELETE
AS
DELETE FROM database2.dbo.table2 childTable
WHERE bar = 4 AND exists (SELECT id FROM deleted where deleted.id = childTable.id)
GO
Source: Stackoverflow.com