所以我试图创建一个可以改变记录时间戳的触发器,到目前为止
create or replace TRIGGER job_date_set
AFTER INSERT OR UPDATE OF start_date, closing_date ON jobs
FOR EACH ROW
BEGIN
IF UPDATING THEN
CASE
WHEN :OLD.closing_date != :NEW.closing_date THEN
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY')||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
WHEN :OLD.start_date != :NEW.start_date THEN
UPDATE jobs
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY') ||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
END CASE;
END IF;
IF INSERTING THEN
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
END IF;
END;
这里是错误的描述:编译失败,第17行(16:54:27)与编译错误相关的行号是相对于第一个BEGIN语句的。这仅影响数据库触发器的编译。PL / SQL:ORA-00933:SQL命令未正确结束编译失败,第15行(16:54:27)与编译错误相关的行号是相对于第一个BEGIN语句的。这仅影响数据库触发器的编译。PL / SQL:忽略了SQL语句
听起来,它认为我的if语句没有正确关闭,但是我不知道我哪里出错了
代替:
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS')
SET start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
它的:
UPDATE jobs
SET closing_date = to_date(to_date(:NEW.closing_date,'DD/MON/YYYY') ||' 23:59:59','DD/MON/YYYY HH24:MI:SS'),
start_date = to_date(to_date(:NEW.start_date,'DD/MON/YYYY')||' 00:00:00','DD/MON/YYYY HH24:MI:SS')
WHERE :OLD.job_id = job_id;
即有一个逗号而不是第二个SET关键字。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句