1.报错复现

system用户下执行如下SQL：

-- Create table
CREATE TABLE PERSON.PERSON
(
  PERSONID INTEGER NOT NULL,
  SEX      CHAR(1) NOT NULL,
  NAME     VARCHAR2(50) NOT NULL,
  EMAIL    VARCHAR2(50),
  PHONE    VARCHAR2(25)
)

-- CREATE SEQUENCE 
CREATE SEQUENCE SEQ_USERINFO
MINVALUE 1
MAXVALUE 9999999999999999999999999999
START WITH 244
INCREMENT BY 1
CACHE 20;



-- 创建触发器
CREATE OR REPLACE TRIGGER TRG_UPD
  AFTER UPDATE OF NAME, PHONE ON PERSON.PERSON
BEGIN
  INSERT INTO PERSON.PERSON
  VALUES
    (SEQ_USERINFO.NEXTVAL, 'F', '触发了', '', '');
END;
复制

1.1 编译报错ORA-01031

1.2 检查对象状态和权限

ALTER TRIGGER TRG_UPD COMPILE;--再次编译对象

SELECT T.STATUS,T.* FROM DBA_OBJECTS T WHERE T.OBJECT_NAME='TRG_UPD';--显示INVALID

SQL> col GRANTEE for a15
SQL> col PRIVILEGE for a25
SQL> SELECT * FROM DBA_SYS_PRIVS  T WHERE T.GRANTEE='SYSTEM';--默认没有插入和更新其他表的权限

GRANTEE         PRIVILEGE                 ADMIN_OPT
--------------- ------------------------- ---------
SYSTEM          GLOBAL QUERY REWRITE      NO
SYSTEM          CREATE MATERIALIZED VIEW  NO
SYSTEM          CREATE TABLE              NO
SYSTEM          UNLIMITED TABLESPACE      YES
SYSTEM          SELECT ANY TABLE          NO
复制

2.处理办法

SQL> GRANT update ANY TABLE TO system;

Grant succeeded.

SQL> grant insert any table to system;

Grant succeeded.

SQL> ALTER TRIGGER SYSTEM.TRG_UPD COMPILE;

Trigger altered.

SQL> col OBJECT_NAME for a15
SQL> col OWNER for a10
SQL> SELECT T.STATUS,T.OBJECT_NAME,T.OWNER FROM DBA_OBJECTS T WHERE T.OBJECT_NAME='TRG_UPD';
STATUS                OBJECT_NAME     OWNER
--------------------- --------------- ----------
VALID                 TRG_UPD         SYSTEM

复制

3.验证效果

– 验证触发器是否生效
UPDATE PERSON.PERSON SET NAME = ‘ZHANGSAN’ WHERE PERSONID = ‘1’;

– 验证触发器是否工作：
SELECT * FROM PERSON.PERSON WHERE NAME = ‘触发了’;

– 清除数据防止干扰后面测试：
DELETE FROM PERSON.PERSON WHERE NAME = ‘触发了’;

4.小结

涉及到权限的事情，一般就是排查系统权限DBA_SYS_PRIVS，角色权限，DBA_ROLE_PRIVS和对象权限。