PL / SQL在触发器内调用一个函数 [英] PL/SQL calling a function inside a trigger

问题描述

我正在尝试使用Oracle PL / SQL创建一个将当前日期存储到变量中的函数。该函数将在触发器内被调用。触发器（除其他外）将把这个变量插入到已经创建的新表中。我的代码符合并运行没有错误，但它不起作用。什么都没发生。触发器是一个ON DELETE触发器，所以当我从原始表中删除一行时，它只是停留。任何线索我缺少什么？
$ b

函数：

pre $ CREATE OR REPLACE FUNCTION get_date（ i_stdid archive_student.stdid％TYPE）
返回日期
AS
v_date DATE;
BEGIN
SELECT CURRENT_DATE INTO v_date FROM DUAL;
RETURN v_date;
END;

触发器内的函数调用：

<$ p $创建或替换TRIGGER ARCHIVE_DELETED_STUDENT
在学生上删除后
为每行
DECLARE
v_date archive_student.change_date％TYPE;
BEGIN
- 其他if语句可以正常工作
v_date：= get_date（：old.stdid）;
INSERT INTO archive_student（change_date）VALUES（v_date）;
END;

解决方案

您做得很好，请检查这个SQLFiddle 。

只有一件事 - 当您插入 archive_student stdid c>因此插入后它为空。

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;

解决方案

You doing well, check this SQLFiddle.

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

这篇关于PL / SQL在触发器内调用一个函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！