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

What is `SELECT account_number into suppose to do`exactly. shouldn't that be a parameter for the function Like `Day_21_bankTriggerFunction(NEw.account_number) ` — nbk, Mar 29 '22 at 23:45
I don't think you can pass args in trigger functions. i get an error my thought was if i ran delete from account where account_number = 12345 12345 would be passed into act — Evan Day, Mar 29 '22 at 23:47
yes you can pass that is a basic feature of functions. So you get an error which one exactly — nbk, Mar 29 '22 at 23:51
1) You can use args see `TG_ARGV[]` here [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html) 2) This trigger is basically trying to be a `FOREIGN KEY ... ON DELETE CASCADE`, so why not just use that? — Adrian Klaver, Mar 29 '22 at 23:51
sorry! this is all pretty new to me...i'll take a look and see if i can figure it out. thank you — Evan Day, Mar 29 '22 at 23:53
@nbk. Read [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html). You can use arguments you just can't declare them in the function signature, per docs: `... it as a function with no arguments`. — Adrian Klaver, Mar 29 '22 at 23:54
@AdrianKlaver i know that thank you. but my coment should lead to read about functions and parameters — nbk, Mar 29 '22 at 23:57
@nbk are you able to provide a visual example? I'm not really understanding the docs — Evan Day, Mar 30 '22 at 00:15
Don't even do this just use a Foreign Key between the tables and let that do the job. — Adrian Klaver, Mar 30 '22 at 00:21
If you insist on using this function then ditch the `DECLARE` and just do `DELETE FROM depositor WHERE depositor.account_number = OLD.account_number;` and `RETURN OLD;` per docs : '... the usual idiom in DELETE triggers is to return OLD.'. Because as per docs: 'The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null.'. So `RETURN OLD` is really just a placeholder. — Adrian Klaver, Mar 30 '22 at 00:32

score 0 · Answer 1 · answered Mar 30 '22 at 03:26

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.

PostgreSQL trigger function not working properly

1 Answers1