触发器将插入行值设置为空 [英] Trigger set insert row values to null

问题描述

所以我正在尝试编写一个触发器，当某些内容插入数据库时​​，如果超过特定时间，将行值设置为空

So I'm trying to write a trigger that when something is inserted into the database if it is over a specific time set the row values to null

CREATE OR REPLACE TRIGGER hi
  AFTER INSERT OR UPDATE OF CLASS_TIME ON class
  FOR EACH ROW 
BEGIN 
    IF (:NEW.CLASS_TIME < '09:00' ) 
    OR (:NEW.CLASS_TIME > '18:00' )
    THEN    
        RAISE_APPLICATION_ERROR(-20000, 'Due to low attendance no class cannot be scheduled at that time');
        SET NEW.STAFFNO = NULL; 
        SET NEW.CLASS_DAY = NULL; 
        SET NEW.CLASS_TYPE = NULL; 
        SET NEW.ROOMNUM = NULL;

    END IF; 
END; 

我在网上找到的所有内容都表明我得到的是正确的，但我在 STAFFNO 上得到了错误.提前感谢您的任何意见.

All that I have found online shows that what Ive got is correct but I get the error on the STAFFNO. Thanks in advance for any input.

推荐答案

首先你要决定:

• 要么您希望插入或更新在不希望的时间完成时失败
• 或您希望处理插入或更新，但随后将列设置为空.

• either you want the insert or update to fail when it's being done at undesired time
• or you want the insert or update to be processed, but with the columns set to null then.

你不能同时拥有.

如果您想将列设置为 NULL，这应该是一个 BEFORE INSERT/UPDATE 触发器，以便将更改的列写入表中.(在 AFTER INSERT/UPDATE 触发器中，将字段设置为某个值不会有任何影响，因为它们没有被写入.)

In case you want to set the columns to NULL, this should be a BEFORE INSERT/UPDATE trigger, so the changed columns get written to the table. (In an AFTER INSERT/UPDATE trigger setting the fields to some value would not have any effect, becase they are not written.)

然后 SET NEW.STAFFNO = NULL; 不是有效的 PL/SQL，必须是 :NEW.STAFFNO := NULL; 代替.

Then SET NEW.STAFFNO = NULL; is no valid PL/SQL, that would have to be :NEW.STAFFNO := NULL; instead.

CREATE OR REPLACE TRIGGER hi
  BEFORE INSERT OR UPDATE OF class_time ON class
  FOR EACH ROW 
WHEN (new.class_time NOT BETWEEN '09:00' AND '18:00')
BEGIN 
  :new.staffno := null; 
  :new.class_day := null; 
  :new.class_type := null; 
  :new.roomnum := null;
END; 

这篇关于触发器将插入行值设置为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！