SQL Server ON DELETE Trigger

p1iqtdky 于 2023-10-15 发布在 SQL Server

关注(0)|答案(5)|浏览(131)

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

sql-server

来源：https://stackoverflow.com/questions/9996643/sql-server-on-delete-trigger

5条答案

按热度按时间

7ivaypg91#

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

赞(0）回复(0）举报 2023-10-15

gpnt7bae2#

Better to use:

DELETE tbl FROM tbl INNER JOIN deleted ON tbl.key=deleted.key

赞(0）回复(0）举报 2023-10-15

rta7y2nd3#

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)

赞(0）回复(0）举报 2023-10-15

v1uwarro4#

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

赞(0）回复(0）举报 2023-10-15

yb3bgrhw5#

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

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server ON DELETE Trigger

5条答案

相关问题

热门标签

最新问答