Oracle trigger Demo

  CREATE TABLE "figo"."AAA_LOG"
   ( "AAA_NO" NUMBER(*,0),
 "LOGS" VARCHAR2(4000 BYTE),
 "LOG_DT" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 

  CREATE TABLE "figo"."AAA"
   ( "AAA_NO" NUMBER(*,0),
 "COMMENTS" VARCHAR2(400 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
   COMMENT ON COLUMN "figo"."AAA"."AAA_NO" IS 'AAA_NO just for trigger test';
 
   COMMENT ON TABLE "figo"."AAA"  IS 'just for trigger test';
 
  CREATE OR REPLACE TRIGGER "figo"."FF_TRIGGER_AAA"
before INSERT ON AAA
referencing old as old_value
new as new_value
for each row
 WHEN (new_value.AAA_no>100) DECLARE v_comment VARCHAR2(4000 BYTE);v_no number;
BEGIN
       v_comment := 'AAA_no is big, comment as:' || :new_value.comments;
       v_no := :new_value.AAA_NO + 1000;
       insert into AAA_log(AAA_NO,LOGS,LOG_DT) VALUES(v_no,v_comment,sysdate);
END;

/
ALTER TRIGGER "figo"."FF_TRIGGER_AAA" ENABLE;
 

 

转载于:https://www.cnblogs.com/feishunji/archive/2011/08/05/2128344.html