触发器例子

1.操作对象表

create table jk_test5 ( id number not null primary key,person_id varchar2(20),name varchar2(30),readflag number,note varchar2(200));

2.日志表

create table jk_test5_log (read_time date,read_user varchar2(30),user_ip varchar2(50),note varchar2(200));

3.触发器

CREATE OR REPLACE TRIGGER JK_TEST5_U
AFTER UPDATE ON jk_test5 FOR EACH ROW 
DECLARE
BEGIN 
   if (:NEW.readflag != :OLD.readflag) or ((:NEW.readflag is null) and (:OLD.readflag is not null)) or ((:NEW.readflag is not null) and (:OLD.readflag is null)) then
      insert into jk_test5_log (read_time,read_user,user_ip)
      values (sysdate,user,sys_context('USERENV','IP_ADDRESS'));
   end if;
END;
select * from jk_test5 for update; 
select * from jk_test5_log;
DECLARE
m number(6);
BEGIN
   m:=0;
   FOR c IN (
  SELECT * FROM aaa_jim T
   ) 
   LOOP
     UPDATE md_payinfo M SET m.readstate='0' WHERE c.str1=m.id;
     m:=m+1;
   END LOOP;
        dbms_output.put('处理信息条数：');
        dbms_output.put_line(m);
END;