oracle触发器中判断记录是否为空

我开始写了一个触发器,要判断SSO_SP_LIST表中MASTER_SESS_ID字段值与我要在SSO_MASTERS_INF
表中删除的记录的MASTER_SESS_ID字段值相等的记录是否为空,我select了该记录的MASTER_SESS_ID字段,放入变量v(字段MASTER_SESS_ID类型),判断if v is not null,结果v为空时,触发器执行到这里就出错。

于是我将变量改为整型,让它返回记录的count值,判断是否大于0,ok,触发器照我的想法跑了,嘿嘿。

原来出错的触发器:

CREATE OR REPLACE TRIGGER TIG_BEF_DELETE_SSO_MASTERS
BEFORE DELETE
ON SSO_MASTERS_INF

FOR EACH ROW
DECLARE
   V SSO_SP_LIST.MASTER_SESS_ID%TYPE
BEGIN
   UPDATE SSO_MASTERS_HIS SET SSO_MASTERS_HIS.TIME_EXIT=SYSDATE WHERE SSO_MASTERS_HIS.MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
   SELECT MASTER_SESS_ID INTO V FROM SSO_SP_LIST WHERE SSO_SP_LIST.MASTER_SESS_ID=:OLD.MASTER_SESS_ID;

   IF V IS NOT NULL THEN
       DELETE FROM SSO_SP_LIST WHERE MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
       UPDATE SSO_SP_LIST_HIS SET TIME_SP_EXIT=SYSDATE WHERE MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
   END IF;
end TIG_BEF_DELETE_SSO_MASTERS;

修改后的触发器

CREATE OR REPLACE TRIGGER TIG_BEF_DELETE_SSO_MASTERS
BEFORE DELETE
ON SSO_MASTERS_INF

FOR EACH ROW
DECLARE
   V int;
BEGIN
   UPDATE SSO_MASTERS_HIS SET SSO_MASTERS_HIS.TIME_EXIT=SYSDATE WHERE SSO_MASTERS_HIS.MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
   SELECT count(*) INTO V FROM SSO_SP_LIST WHERE SSO_SP_LIST.MASTER_SESS_ID=:OLD.MASTER_SESS_ID;

   IF V >0 THEN
       DELETE FROM SSO_SP_LIST WHERE MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
       UPDATE SSO_SP_LIST_HIS SET TIME_SP_EXIT=SYSDATE WHERE MASTER_SESS_ID=:OLD.MASTER_SESS_ID;
   END IF;
END TIG_BEF_DELETE_SSO_MASTERS;

展开阅读全文

没有更多推荐了,返回首页