ALTER TRIGGER

说明

更改触发器属性或编译触发器

语法

alter_trigger ::=

参数

IF EXISTS

修改触发器时，若存在同名触发器则进行修改，否则会跳过操作，且会打印NOTICE提示信息

schema

触发器所属的模式名

tgname

要更改的触发器名

ENABLE

启用触发器

DISABLE

禁用触发器

newname

更改后的触发器名

COMPILE

显式地编译触发器。

注解

ALTER TRIGGER只能开关触发器，修改触发器名称或编译触发器，不能改变触发器的定义。

只有表的拥有者才能更改触发器属性。

不允许修改RI约束触发器。（参考示例）

触发器是否启用不影响其编译结果，且编译结果不影响其启用/禁用。

示例

示例1: 重命名触发器

--  清理环境
DROP TRIGGER trg1;
DROP TRIGGER trg2;
DROP TABLE tab1 CASCADE;

--  创建表
CREATE TABLE tab1 (a INT);

--  创建触发器
CREATE TRIGGER trg1
BEFORE INSERT ON tab1 FOR EACH ROW
BEGIN
    NULL;
END;
/


--  重命名触发器
ALTER TRIGGER trg1 RENAME TO trg2;


--  删除触发器和表
DROP TRIGGER trg2;
DROP TABLE tab1;

-- 触发器不存在时，重命名触发器
-- 使用 IF EXISTS 时，跳过操作，不报错
ALTER TRIGGER IF EXISTS trg2 RENAME TO trg3;

-- 不使用 IF EXISTS 时，语句报错
ALTER TRIGGER trg2 RENAME TO trg3;
ERROR, 触发器 "TRG2" 不存在或无权访问

示例2: 启用/禁用触发器

--  清理环境
DROP TRIGGER trg2;
DROP TABLE tab2 CASCADE;


--  创建表
CREATE TABLE tab2 (id INT, a INT);

--  创建触发器
CREATE TRIGGER trg2
BEFORE INSERT OR UPDATE ON tab2 FOR EACH ROW
BEGIN
    IF NEW.a ISNULL THEN
        RAISE EXCEPTION 'a cannot be NULL value';
    END IF;
END;
/


--  触发器处于启用状态
--  插入 NULL 失败, 触发器抛出异常
INSERT INTO tab2 VALUES(1, NULL);
ERROR, a cannot be NULL value



SELECT * FROM tab2 ORDER BY id;
ID(int)      |A(int)      |
总数目：0


--  禁用触发器, 插入 NULL 成功
ALTER TRIGGER trg2 DISABLE;

INSERT INTO tab2 VALUES(2, NULL);


SELECT * FROM tab2 ORDER BY id;
ID(int)      |A(int)      |
-------------------------
2            |null        |
总数目：1


--  启用触发器, 插入 NULL 失败
ALTER TRIGGER trg2 ENABLE;

INSERT INTO tab2 VALUES(3, NULL);
ERROR, a cannot be NULL value



SELECT * FROM tab2 ORDER BY id;
ID(int)      |A(int)      |
-------------------------
2            |null        |
总数目：1


--  删除触发器和表
DROP TRIGGER trg2;
DROP TABLE tab2;

示例3: 不允许修改 RI 约束触发器

--  清理环境
DROP TRIGGER trg3;
DROP PROCEDURE proc3;
DROP PROCEDURE proc4;
DROP TABLE tab4 CASCADE;
DROP TABLE tab3 CASCADE;

--  创建表
CREATE TABLE tab3 (id INT PRIMARY KEY);
CREATE TABLE tab4 (a INT REFERENCES tab3(id));



--  查看触发器状态
SELECT TGISCONSTRAINT FROM SYS_TRIGGER T, SYS_CLASS R 
WHERE T.BASEOBJID = R.OID AND R.RELNAME IN ('TAB3', 'TAB4');
TGISCONSTRAINT(boolean)      |
-----------------------------
true                         |
-----------------------------
true                         |
-----------------------------
true                         |
总数目：3


--  重命名 RI 触发器
CREATE PROCEDURE proc3 AS
DECLARE
    cur        REFCURSOR;
    tigger_name VARCHAR(256);
BEGIN
    OPEN cur FOR SELECT TGNAME FROM SYS_TRIGGER T, SYS_CLASS R 
        WHERE T.BASEOBJID = R.OID AND R.RELNAME IN ('TAB3', 'TAB4');
    LOOP
       FETCH cur INTO tigger_name;
       EXIT WHEN NOT FOUND;
       BEGIN
           EXECUTE 'ALTER TRIGGER '|| tigger_name ||' RENAME TO trg3';
           EXCEPTION 
           WHEN OTHERS THEN
           BEGIN
               RAISE NOTICE 'RENAME TRIGGER FAILED!';
           END;
       END;
    END LOOP;
    CLOSE cur;
END;

/

EXEC proc3;
NOTICE, RENAME TRIGGER FAILED!
NOTICE, RENAME TRIGGER FAILED!
NOTICE, RENAME TRIGGER FAILED!



--  查看触发器状态
SELECT TGISCONSTRAINT FROM SYS_TRIGGER T, SYS_CLASS R 
WHERE T.BASEOBJID = R.OID AND R.RELNAME IN ('TAB3', 'TAB4');
TGISCONSTRAINT(boolean)      |
-----------------------------
true                         |
-----------------------------
true                         |
-----------------------------
true                         |
总数目：3


--  禁用 RI 触发器
CREATE PROCEDURE proc4 AS
DECLARE
    cur        REFCURSOR;
    tigger_name VARCHAR(256);
BEGIN
    OPEN cur FOR SELECT TGNAME FROM SYS_TRIGGER T, SYS_CLASS R 
        WHERE T.BASEOBJID = R.OID AND R.RELNAME IN ('TAB3', 'TAB4');
    LOOP
       FETCH cur INTO tigger_name;
       EXIT WHEN NOT FOUND;
       BEGIN
           EXECUTE 'ALTER TRIGGER '|| tigger_name ||' DISABLE';
           EXCEPTION 
           WHEN OTHERS THEN
           BEGIN
               RAISE NOTICE 'DISABLE TRIGGER FAILED!';
           END;
       END;
    END LOOP;
    CLOSE cur;
END;

/

EXEC proc4;
NOTICE, DISABLE TRIGGER FAILED!
NOTICE, DISABLE TRIGGER FAILED!
NOTICE, DISABLE TRIGGER FAILED!



--  查看触发器状态
SELECT TGISCONSTRAINT FROM SYS_TRIGGER T, SYS_CLASS R 
WHERE T.BASEOBJID = R.OID AND R.RELNAME IN ('TAB3', 'TAB4');
TGISCONSTRAINT(boolean)      |
-----------------------------
true                         |
-----------------------------
true                         |
-----------------------------
true                         |
总数目：3


--  删除存储过程和表
DROP PROCEDURE proc3;
DROP PROCEDURE proc4;
DROP TABLE tab4;
DROP TABLE tab3;

示例4: 编译触发器

drop table t1;
drop table t2;
drop trigger tri1;

create table t1(a int);
create table t2(a int);

CREATE OR REPLACE TRIGGER tri1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  insert into t2 values(1);
END tri1;

/

select status from sys_object_status where oid in(select oid from sys_trigger where tgname='TRI1');
STATUS(int)      |
-----------------
1                |
总数目：1

drop table t2;


alter trigger tri1 compile;
select status from sys_object_status where oid in(select oid from sys_trigger where tgname='TRI1');
STATUS(int)      |
-----------------
0                |
总数目：1

create table t2(b int);
alter trigger tri1 compile;

select status from sys_object_status where oid in(select oid from sys_trigger where tgname='TRI1');
STATUS(int)      |
-----------------
1                |
总数目：1

drop trigger tri1;
drop table t1;
drop table t2;