CODEWITHSUNDEEP
oracle-databasefunctionplsqltriggers
teppuus
teppuus

Reputation: 89

PL/SQL calling a function inside a trigger

I am trying to create a function using Oracle PL/SQL that stores the current date into a variable. This function will be called inside a trigger. The trigger (among other things) will insert this variable into a new table that has already been created. My code complies and runs without errors, but it doesn't work. Nothing happens. The trigger is an ON DELETE trigger, so when I delete one row from the original table, it just stays. Any clues what I am missing? Thank you.

Function:

    CREATE OR REPLACE FUNCTION get_date (i_stdid archive_student.stdid%TYPE)
    RETURN date
    AS
       v_date DATE;
    BEGIN
       SELECT CURRENT_DATE INTO v_date FROM DUAL; 
    RETURN v_date;
    END;

Function call inside trigger:

    CREATE OR REPLACE TRIGGER ARCHIVE_DELETED_STUDENT
    AFTER DELETE ON STUDENT
    FOR EACH ROW
    DECLARE 
        v_date archive_student.change_date%TYPE;
    BEGIN
        -- other if statements here that are working properly
        v_date := get_date(:old.stdid);
        INSERT INTO archive_student (change_date) VALUES (v_date);
    END;

Upvotes: 2

Views: 13485

Answers (1)

ThinkJet
ThinkJet

Reputation: 6735

You doing well, check this SQLFiddle.

Only one thing - you missed stdid while inserting into archive_student so it is null after insert.

Upvotes: 2

Related Questions