PostgreSQL trigger function not working properly

Question

I have a trigger function:

CREATE OR REPLACE FUNCTION Day_21_bankTriggerFunction()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$

DECLARE
act VARCHAR(30);

BEGIN
SELECT account_number INTO act
DELETE FROM depositor
WHERE depositor.account_number = act;

    RETURN act;
    END;

$$;
\`

and then I have a trigger:

CREATE TRIGGER Day_21_bankTrigger AFTER DELETE on account FOR EACH ROW EXECUTE PROCEDURE Day_21_bankTriggerFunction()

The thought behind this is that is an account was deleted from the account table then this should trigger the function to run and then delete all records on the depositor table where that account is present.

I can create the trigger function and trigger without an issues but if I attempt to delete an account from the account table...I still see the account # in the depositor table when I shouldn't.

Any thoughts?

above is what I tried. Expected results would be to delete an account from the account table and then the trigger function should kick off and remove that account from the depositor table

Answer 1

Usually, relationships of any mode between tables are created by foreign keys. This is the best way and are standards for DBs. Using foreign keys you can control your data. SQL sample:

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE SET NULL
      ON UPDATE SET NULL
);

Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.

Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses. The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. We’ll focus on the ON DELETE action. PostgreSQL supports the following actions after updating or deleting:

• SET NULL (Set values to NULL if data exists on referencing table)
• SET DEFAULT (Set values to DEFAULT VALUES of this field if data exists on referencing table)
• RESTRICT (Similar to NO ACTION)
• NO ACTION (Can not update or delete data if exists on referencing table)
• CASCADE (Delete all data if exists on referencing table)

I wrote for you a sample trigger function:

CREATE OR REPLACE FUNCTION Day_21_bankTriggerFunction()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare
    act varchar(30);
begin
    -- get account_number from deleted record
    act = old.account_number; 

    -- SECTION-1 :: Protect deleting if existing data
    if (exists(select 1 from depositor where account_number = act)) then 
        return null;
    end if;
    -- SECTION-1 :: END 


    -- SECTION-2 :: Delete all data in the anothers table if exists */
    delete from depositor where account_number = act;
    return old; 
    -- SECTION-2 :: END 

end
$function$;


CREATE TRIGGER Day_21_bankTrigger 
BEFORE DELETE on account 
FOR EACH ROW EXECUTE PROCEDURE Day_21_bankTriggerFunction();

Inside my trigger function, I have written two types of SQL codes. (SECTION-1, SECTION-2). You must choose one of them.