postgresql - 如何更改 SQL Server 而不是删除触发器到 PostgreSQL

Question

我想在 PostgreSQL 中创建与在 SQL Server 中相同类型的触发器。

我尝试更改代码以使其正常运行，但出现了很多错误，但没有说明哪里出了问题，但不知何故，我设法更改了可以运行的代码，但随后调用触发器时出现错误:

relation old doesn't exist

SQL Server 触发器:

CREATE TRIGGER DeleteUser 
ON users
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (SELECT * 
               FROM deleted d
               LEFT JOIN orders as o on o.user_id = d.id
               WHERE o.id IS NOT NULL)
    BEGIN
        ROLLBACK
        RAISERROR('Cannot delete this record: user already ordered something',16,1)
    END
    ELSE
    BEGIN
        DELETE FROM users
        WHERE EXISTS (SELECT * FROM deleted d WHERE d.id = users.id)

        PRINT('Deleted!')
    END
END
GO

为 Postgres 改编的代码:

功能:

CREATE FUNCTION delete_user()
RETURNS TRIGGER
AS $$
BEGIN
IF EXISTS (
    SELECT * 
    FROM old d
    LEFT JOIN orders as o on o.user_id = d.id
    where o.id is not null
    ) THEN
    RAISE EXCEPTION 'Cannot delete this record: user already ordered something';
ELSE
      DELETE FROM users
      WHERE EXISTS (Select * from deleted d where d.id = users.id);
      RAISE NOTICE 'Deleted!';
END IF;
END;
$$
LANGUAGE plpgsql;

触发器:

CREATE TRIGGER deletion_of_user
    BEFORE DELETE
    ON public.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.delete_user();

Answer 1

您不需要在触发代码中删除。由于它是一个 before 触发器，只需返回 old 记录就足以使删除发生。

您创建了一个行级别触发器(SQL Server 没有的东西)，所以旧 记录只是一行，您不能从中选择它。只需在 SELECT 语句的 WHERE 子句中使用它即可。

CREATE FUNCTION delete_user()
RETURNS TRIGGER
AS $$
BEGIN
IF EXISTS (
    SELECT * 
    FROM orders as o 
    where o.user_id = old.id; --<< no need for a join
    ) THEN
    RAISE EXCEPTION 'Cannot delete this record: user already ordered something';
ELSE
    RAISE NOTICE 'Deleted!';
    return old; --<< tell Postgres to continue with the delete
END IF;
END;
$$
LANGUAGE plpgsql;

---

但是(错误地)为此使用触发器是错误的方法(在 Postgres 中和在 SQL Server 中一样)。您应该从引用用户表的订单表中声明一个外键，这将同样防止删除 - 并且可能更有效。