How to change SQL Server instead of delete trigger to PostgreSQL

Question

I would like to make the same kind of trigger in PostgreSQL as in SQL Server.

I tried changing code to function and had a lot of errors not specifying whats wrong but somehow I managed to change my code that it could run but then invoking trigger I get an error:

relation old doesn't exist

SQL Server trigger:

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

Adapted code for Postgres:

function:

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;

trigger:

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

Answer 1

You don't need to delete in the trigger code. As it is a before trigger, just returning the old record is enough to make the delete happen.

You created a row level trigger (something that SQL Server doesn't have), so the old record is just a single row, you can't select from it. Just use it in the WHERE clause of the SELECT statement.

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;

---

But (mis)using a trigger for this is the wrong approach (in Postgres just as well as in SQL Server). You should declare a foreign key from the orders table referencing the users table which will prevent the deletion just as well - and probably much more efficiently.