代码
比这篇新的文章:
发芽网根目录的.htaccess
比这篇旧的文章: inputtest
作者: 苏滋文, 点击377次, 评论(0), 收藏者(0), , 打分:
所有评论，共0条：( 我也来说两句)
比这篇旧的文章: inputtest
意见表数据变化的触发器
语言: SQL, 标签: 无 2009/02/25发布 1年前更新作者: 苏滋文, 点击377次, 评论(0), 收藏者(0), , 打分:
SQL语言: 意见表数据变化的触发器
01 CREATE TABLE TMP_LOG_EMBA_GW_OPINION
02 (
03 PK_ID NUMBER(19) NOT NULL,
04 OPERATORTYPE VARCHAR2(20),
05 OPERATORTIME VARCHAR2(20),
06 GWID NUMBER(19),
07 USERID VARCHAR2(32 BYTE),
08 USERNAME VARCHAR2(50 BYTE),
09 STEPID NUMBER(10) ,
10 STEPNAME VARCHAR2(60 BYTE),
11 RTIME VARCHAR2(14 BYTE),
12 STATE VARCHAR2(2 BYTE),
13 SUPERDEPT VARCHAR2(20 BYTE),
14 VIEWTYPE VARCHAR2(25 BYTE),
15 TACHEID VARCHAR2(41 BYTE),
16 DEPT VARCHAR2(50 BYTE),
17 OPINION VARCHAR2(3000 BYTE),
18 SIGNDATA VARCHAR2(2000 BYTE),
19 CERT VARCHAR2(2000 BYTE),
20 SUBWFID VARCHAR2(20 BYTE),
21 SUPER_STEPID NUMBER(4),
22 PRIMARY KEY
23 (PK_ID)
24 );
25
26 -----创建序列
27 CREATE SEQUENCE SEQUENCE_LOG_EMBA_GW_OPINION
28 START WITH 1
29 MAXVALUE 999999999999999999999999999
30 MINVALUE 1
31 NOCYCLE
32 NOCACHE
33 NOORDER;
34
35 ----添加主键自动生成的序列
36 CREATE OR REPLACE TRIGGER TRIGGER_SEQNO_LOG_OPINION before insert on TMP_LOG_EMBA_GW_OPINION for each row
37 WHEN (
38 new.pk_id is null
39 )
40 begin
41 select SEQUENCE_LOG_EMBA_GW_OPINION.nextval into :new.pk_id from dual;
42 end TRIGGER_SEQNO_LOG_OPINION ;
43
44
45 ----创建意见表变化时进行触发
46 CREATE OR REPLACE TRIGGER TRIGGER_TMP_LOG_OPINION
47 BEFORE INSERT OR UPDATE OR DELETE
48 ON EMBA_GW_OPINION
49 FOR EACH ROW
50 BEGIN
51 IF INSERTING THEN
52 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
53 VALUES( 'add',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
54 ELSIF UPDATING THEN
55 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
56 VALUES( 'update',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
57 ELSIF DELETING THEN
58 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
59 VALUES( 'delete',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
60 END IF;
61 END TRIGGER_TMP_LOG_OPINION;
02 (
03 PK_ID NUMBER(19) NOT NULL,
04 OPERATORTYPE VARCHAR2(20),
05 OPERATORTIME VARCHAR2(20),
06 GWID NUMBER(19),
07 USERID VARCHAR2(32 BYTE),
08 USERNAME VARCHAR2(50 BYTE),
09 STEPID NUMBER(10) ,
10 STEPNAME VARCHAR2(60 BYTE),
11 RTIME VARCHAR2(14 BYTE),
12 STATE VARCHAR2(2 BYTE),
13 SUPERDEPT VARCHAR2(20 BYTE),
14 VIEWTYPE VARCHAR2(25 BYTE),
15 TACHEID VARCHAR2(41 BYTE),
16 DEPT VARCHAR2(50 BYTE),
17 OPINION VARCHAR2(3000 BYTE),
18 SIGNDATA VARCHAR2(2000 BYTE),
19 CERT VARCHAR2(2000 BYTE),
20 SUBWFID VARCHAR2(20 BYTE),
21 SUPER_STEPID NUMBER(4),
22 PRIMARY KEY
23 (PK_ID)
24 );
25
26 -----创建序列
27 CREATE SEQUENCE SEQUENCE_LOG_EMBA_GW_OPINION
28 START WITH 1
29 MAXVALUE 999999999999999999999999999
30 MINVALUE 1
31 NOCYCLE
32 NOCACHE
33 NOORDER;
34
35 ----添加主键自动生成的序列
36 CREATE OR REPLACE TRIGGER TRIGGER_SEQNO_LOG_OPINION before insert on TMP_LOG_EMBA_GW_OPINION for each row
37 WHEN (
38 new.pk_id is null
39 )
40 begin
41 select SEQUENCE_LOG_EMBA_GW_OPINION.nextval into :new.pk_id from dual;
42 end TRIGGER_SEQNO_LOG_OPINION ;
43
44
45 ----创建意见表变化时进行触发
46 CREATE OR REPLACE TRIGGER TRIGGER_TMP_LOG_OPINION
47 BEFORE INSERT OR UPDATE OR DELETE
48 ON EMBA_GW_OPINION
49 FOR EACH ROW
50 BEGIN
51 IF INSERTING THEN
52 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
53 VALUES( 'add',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
54 ELSIF UPDATING THEN
55 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
56 VALUES( 'update',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
57 ELSIF DELETING THEN
58 INSERT INTO TMP_LOG_EMBA_GW_OPINION (OPERATORTYPE, OPERATORTIME, GWID, USERID, USERNAME, STEPID, STEPNAME,RTIME,STATE,SUPERDEPT,VIEWTYPE,TACHEID,DEPT,OPINION,SIGNDATA,CERT,SUBWFID,SUPER_STEPID)
59 VALUES( 'delete',TO_CHAR(sysdate,'YYYYMMDDHH24MISS'), :NEW.GWID, :NEW.USERID, :NEW.USERNAME, :NEW.STEPID, :NEW.STEPNAME, :NEW.RTIME, :NEW.STATE, :NEW.SUPERDEPT, :NEW.VIEWTYPE, :NEW.TACHEID, :NEW.DEPT, :NEW.OPINION, :NEW.SIGNDATA, :NEW.CERT, :NEW.SUBWFID, :NEW.SUPER_STEPID);
60 END IF;
61 END TRIGGER_TMP_LOG_OPINION;
所有评论，共0条：( 我也来说两句)