SQL Server ON DELETE Trigger
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
CREATE TRIGGER sampleTrigger
ON database1.dbo.table1
FOR DELETE
AS
DELETE FROM database2.dbo.table2
WHERE bar = 4 AND ID IN(SELECT deleted.id FROM deleted)
GO
Better to use:
DELETE tbl FROM tbl INNER JOIN deleted ON tbl.key=deleted.key
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)
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