Postgresql delete record involving two tables and store this record in the third record

Question

I'm trying to delete a record from the table 'student', where on a Cascade delete it will remove it from the 'entry' table. But before delete i need to store this record in the third table 'cancel'.

Here is what i worked out so far:

DELETE FROM "CMPS".student
WHERE sno = '1';    

CREATE TRIGGER canceled BEFORE DELETE     
    ON entry    
    FOR EACH ROW    
    EXECUTE PROCEDURE trigger_backup_row    

CREATE OR REPLACE FUNCTION trigger_backup_row(integer)    
    RETURNS trigger AS    
$$    
    BEGIN    
INSERT INTO cancel (eno, excode, sno) values (NEW.eno, NEW.excode, NEW.sno);    
    RETURN NEW;    
    END;    
$$         
   language PLPGSQL    

But comes back with an errors. Any help will be much appreciated.

Answer 1

I suppose you need:

CREATE OR REPLACE FUNCTION trigger_backup_row()    
    RETURNS trigger AS    
$$    
    BEGIN    
INSERT INTO cancel (eno, excode, sno) values (OLD.eno, OLD.excode, OLD.sno);    
    RETURN OLD;    
    END;    
$$         
   language PLPGSQL
;

CREATE TRIGGER canceled BEFORE DELETE     
    ON entry    
    FOR EACH ROW    
    EXECUTE PROCEDURE trigger_backup_row()
;

• trigger function do not use arguments
• on delete yo udon't have any NEW row - just an OLD one