Oracle Trigger -- 用Procedure给数据库中所有表建Trigger

最新推荐文章于 2022-05-04 08:56:28 发布

tower6277

最新推荐文章于 2022-05-04 08:56:28 发布

阅读量724

点赞数

CC 4.0 BY-SA版权

文章标签： oracle 数据库 table delete insert triggers

本文链接：https://blog.youkuaiyun.com/tower6277/article/details/2834645

本文介绍了一种方法，通过遍历数据库表，自动为每个表创建触发器，并提供了一个删除所有表触发器的程序。该方法利用PL/SQL编写，涉及到表的名称、触发器的创建与删除等数据库操作。

1.建Trigger的Procedure--通过遍历User_tables来实现

create or replace procedure CREATE_TABLES_TRIGGER
as
CURSOR UserTable IS SELECT TABLE_NAME FROM USER_TABLES;
row_table_name UserTable%ROWTYPE;
var_table_name VARCHAR(100);
var_trigger_name varchar(100);
var_table_name_temp VARCHAR(100);
var_temp_sql VARCHAR(9999);
BEGIN
    OPEN UserTable;
    LOOP
         FETCH UserTable INTO row_table_name;
         EXIT WHEN UserTable%NOTFOUND;
         var_table_name:=row_table_name.table_name;
         var_trigger_name := var_table_name || '_trigger';
         var_temp_sql:='CREATE or replace trigger '|| var_trigger_name || ' after delete or insert or update on ' || var_table_name
                           || ' declare

                           v_type Changed_Info.Optiontype%type;
                           v_tablename User_tables.table_name%type;
                           v_index varchar(50);
                           BEGIN
                               EXECUTE IMMEDIATE ''select change_index from change_index'' into v_index;
                               IF inserting then --insert trigger
                                  v_type := ''INSERT'';
                                    DBMS_OUTPUT.PUT_line(''INSERT SUCCESS'');
                               ELSIF updating then --update trigger
                                    v_type := ''UPDATE'';
                                    DBMS_OUTPUT.put_line(''UPDATE SUCCESS'');
                               ELSIF deleting then --delete trigger
                                    v_type := ''DELETE'';
                                    Dbms_Output.put_line(''DELETE SUCCESS'');
                               end if;
                               insert into changed_info values (v_index,''' || var_table_name || ''',v_type,sysdate);
                            END;';
        EXECUTE IMMEDIATE var_temp_sql;
    END LOOP;
    CLOSE UserTable;
    COMMIT;
END CREATE_TABLES_TRIGGER;

2.删除所有表的Trigger

CREATE_TABLES_TRIGGER;

create or replace procedure DELETE_TABLES_TRIGGER
as
CURSOR UserTrigger IS SELECT trigger_name FROM User_Triggers;
row_trigger_name UserTrigger%ROWTYPE;
var_trigger_name VARCHAR(100);
var_temp_sql VARCHAR(9999);
BEGIN
    OPEN UserTrigger;
    LOOP
         FETCH UserTrigger INTO row_trigger_name;
         EXIT WHEN UserTrigger%NOTFOUND;
         var_trigger_name:=row_trigger_name.trigger_name;
         var_temp_sql := 'drop trigger ' || var_trigger_name;
         EXECUTE IMMEDIATE var_temp_sql;
    END LOOP;
    CLOSE UserTrigger;
    COMMIT;
END DELETE_TABLES_TRIGGER;

PS:Table SQL

-- Create table CHANGED_INFO
create table CHANGED_INFO
(
OPTIONNUM VARCHAR2(10),
TABLENAME VARCHAR2(15),
OPTIONTYPE VARCHAR2(10),
OPTIONTIME DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

-- Create table CHANGE_INDEX
create table CHANGE_INDEX
(
CHANGE_INDEX NUMBER default 1
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);