Question

oracle插入语句获取系统时间作为表字段的触发器怎么弄

create or replace trigger tri_album--创建相册时间自动生成触发器
before insert on album
for each row when(new.addtime is null)
declare
begin
select to_char(sysdate,"YYYY/MM/DD") into :New.addtime from dual;
end tri_album;
怎么有错误？

Answer 1

可以完全没有问题，下面是我测试的脚本，可以参考看看。
create table test_sysdate(t_id number,t_date date);
create or replace trigger trg_test
before insert on test_sysdate
for each row
when (new.t_date is null)
declare
begin
select sysdate into :new.t_date from dual;
end trg_test;
insert into test_sysdate(t_id)values(1);
commit;

Answer 2

sysdate的话貌似就不用转成'yyyy/MM/DD'的格式了吧

Answer 3

你 addtime 是 char(10)，如果是的话， 把“"”改成“'” 就可以了。
select to_char(sysdate,'YYYY/MM/DD') into :New.addtime from dual;
如果是date 的话，
改成
select sysdate into :New.addtime from dual;

Answer 4

CREATE OR REPLACE TRIGGER tri_album
BEFORE Insert or Update ON album
FOR EACH ROW
DECLARE
BEGIN
:NEW.addtime := current_timestamp;
END;