1,在update的语句后面自动加上 f2=sysdate 就好了。2,建一个触发器来执行update table set f2=sysdate。
参考下 : create or replace trigger xxx after update on table of col for each row declare
begin update set col=sysdate; end xxx;
create or replace trigger tri --test是测试表 before insert or update on test for each row begin --time就是你要更新的那列 select sysdate into :new.time from dual; end tri;
不要在触发器里update 7楼的ok
呵呵 帮你做个例子吧 照猫画虎总没问题了吧 SQL> create table t2(id number,time date);Table created.SQL> insert into t2 values(1,to_date('2009-12-05 12:00:00','YYYY-MM-DD HH24:MI:S S'));1 row created.SQL> insert into t2 values(2,to_date('2009-12-03 12:00:00','YYYY-MM-DD HH24:MI:S S'));1 row created.SQL> commit;Commit complete.SQL> select * from t2; ID TIME ---------- --------------- 1 05-DEC-09 2 03-DEC-09SQL> create or replace trigger tri_11 2 before insert or update on t2 3 for each row 4 begin 5 select sysdate into :new.time from dual; 6 end tri; 7 /Trigger created. --插入 SQL> insert into t2 values(3,to_date('2009-12-01 12:00:00','YYYY-MM-DD HH24:MI:S S'));1 row created.SQL> select * from t2; ID TIME ---------- --------------- 1 05-DEC-09 2 03-DEC-09 3 05-DEC-09SQL> select id,to_char(time,'YYYY-MM-DD HH24:MI:SS') from t2; ID TO_CHAR(TIME,'YYYY- ---------- ------------------- 1 2009-12-05 12:00:00 2 2009-12-03 12:00:00 3 2009-12-05 16:06:12 --这里可以看到 新插入的记录time列被修改为当前日期 --更新 SQL> update t2 set id=4 where id=2;1 row updated.SQL> select id,to_char(time,'YYYY-MM-DD HH24:MI:SS') from t2; ID TO_CHAR(TIME,'YYYY- ---------- ------------------- 1 2009-12-05 12:00:00 4 2009-12-05 16:08:09 --这里日期也变为当前日期了 3 2009-12-05 16:06:12
create or replace trigger tri before insert or update on TB_行政区划表 for each row begin select sysdate into :new.UPDATE_TIME from dual; end tri; 为什么这样报 不正常地定义参数对象。提供了不一致或不完整的信息 的错误信息。 UPDATE_TIME 我设的DATE类型啊
create or replace trigger update_tpssm103_date before update on tpssm103 for each row declare -- local variables here begin if updating then select sysdate into :new.v_date from dual; end if; end update_tpssm103_date;
create or replace trigger xxx
after update on table of col
for each row
declare
begin
update set col=sysdate;
end xxx;
--test是测试表
before insert or update on test
for each row
begin
--time就是你要更新的那列
select sysdate into :new.time from dual;
end tri;
7楼的ok
照猫画虎总没问题了吧
SQL> create table t2(id number,time date);Table created.SQL> insert into t2 values(1,to_date('2009-12-05 12:00:00','YYYY-MM-DD HH24:MI:S
S'));1 row created.SQL> insert into t2 values(2,to_date('2009-12-03 12:00:00','YYYY-MM-DD HH24:MI:S
S'));1 row created.SQL> commit;Commit complete.SQL> select * from t2; ID TIME
---------- ---------------
1 05-DEC-09
2 03-DEC-09SQL> create or replace trigger tri_11
2 before insert or update on t2
3 for each row
4 begin
5 select sysdate into :new.time from dual;
6 end tri;
7 /Trigger created.
--插入
SQL> insert into t2 values(3,to_date('2009-12-01 12:00:00','YYYY-MM-DD HH24:MI:S
S'));1 row created.SQL> select * from t2; ID TIME
---------- ---------------
1 05-DEC-09
2 03-DEC-09
3 05-DEC-09SQL> select id,to_char(time,'YYYY-MM-DD HH24:MI:SS') from t2; ID TO_CHAR(TIME,'YYYY-
---------- -------------------
1 2009-12-05 12:00:00
2 2009-12-03 12:00:00
3 2009-12-05 16:06:12 --这里可以看到 新插入的记录time列被修改为当前日期
--更新
SQL> update t2 set id=4 where id=2;1 row updated.SQL> select id,to_char(time,'YYYY-MM-DD HH24:MI:SS') from t2; ID TO_CHAR(TIME,'YYYY-
---------- -------------------
1 2009-12-05 12:00:00
4 2009-12-05 16:08:09 --这里日期也变为当前日期了
3 2009-12-05 16:06:12
before insert or update on TB_行政区划表
for each row
begin
select sysdate into :new.UPDATE_TIME from dual;
end tri;
为什么这样报 不正常地定义参数对象。提供了不一致或不完整的信息 的错误信息。
UPDATE_TIME 我设的DATE类型啊
是会影响效率吗
最好不要在tigger里update ,是什么意思啊
那建这个触发器还有什么意义
before update on tpssm103
for each row
declare
-- local variables here
begin
if updating then
select sysdate into :new.v_date from dual;
end if;
end update_tpssm103_date;