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 doexactly. shouldn't that be a parameter for the function Like Day_21_bankTriggerFunction(NEw.account_number) — nbk
– nbk, Commented Mar 29, 2022 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
– Evan Day, Commented Mar 29, 2022 at 23:47
yes you can pass that is a basic feature of functions. So you get an error which one exactly — nbk
– nbk, Commented Mar 29, 2022 at 23:51
1) You can use args see TG_ARGV[] here plpgsql trigger 2) This trigger is basically trying to be a FOREIGN KEY ... ON DELETE CASCADE, so why not just use that? — Adrian Klaver
– Adrian Klaver, Commented Mar 29, 2022 at 23:51
Don't even do this just use a Foreign Key between the tables and let that do the job. — Adrian Klaver
– Adrian Klaver, Commented Mar 30, 2022 at 0:21

Ramin Faracov · Accepted Answer · 2022-03-30 03:26:01Z

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.

Collectives™ on Stack Overflow

PostgreSQL trigger function not working properly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related