Oracle 11G无法运行触发器并插入记录

Question

在运行此代码时，我会收到错误消息。

CREATE TABLE superheoes
(
  sh_name VARCHAR2(20)
  );
CREATE OR REPLACE TRIGGER superheroes_audit
BEFORE INSERT OR DELETE OR UPDATE ON superheoes
FOR EACH ROW
ENABLE
DECLARE
v_user VARCHAR2(30);
v_date VARCHAR2(30);
BEGIN
SELECT user,TO_CHAR(sysdate,'DD/MON/YYYY HH24:MI:SS') INTO v_user, v_date FROM dual;
IF INSERTING THEN
INSERT INTO sh_audit (new_name, old_name, user_name, entry_date, operation) 
VALUES (:NEW.sh_name, NULL, v_user, v_date, 'Insert');
ELSIF DELETING THEN
INSERT INTO sh_audit (new_name, old_name, user_name, entry_date, operation) 
VALUES (NULL, :OLD.sh_name, v_user, v_date, 'Delete');
ELSIF UPDATING THEN
INSERT INTO sh_audit (new_name, old_name, user_name, entry_date, operation) 
VALUES ( :NEW.sh_name, :OLD.sh_name, v_user, v_date, 'Update');
END IF;
END;
/

然后插入值：

INSERT INTO superheoes(sh_name) VALUES ('Supe');
======================================================================
I am getting the following Error message:
Error starting at line : 76 in command -
INSERT INTO superheoes(sh_name) VALUES ('Supe')
Error at Command Line : 76 Column : 13
Error report -
SQL Error: ORA-04098: trigger 'HR.SUPERHEROES_AUDIT' is invalid and failed re-
validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    
A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Answer 1

如果我根据触发器创建表，则触发器在您编写时起作用：

CREATE TABLE superheoes (sh_name VARCHAR2(20));
CREATE TABLE sh_audit
(
    new_name                                VARCHAR2(20),
    old_name                                VARCHAR2(20),
    user_name                               VARCHAR2(20),
    entry_date                              VARCHAR2(20),
    operation                               VARCHAR2(20)
);
CREATE OR REPLACE TRIGGER superheroes_audit
    BEFORE INSERT OR DELETE OR UPDATE
    ON superheoes
    FOR EACH ROW
    ENABLE
DECLARE
    v_user                                  VARCHAR2(30);
    v_date                                  VARCHAR2(30);
BEGIN
    SELECT USER, TO_CHAR(SYSDATE, 'DD/MON/YYYY HH24:MI:SS')
      INTO v_user, v_date
      FROM DUAL;
    IF INSERTING
    THEN
        INSERT INTO sh_audit(
                             new_name,
                             old_name,
                             user_name,
                             entry_date,
                             operation
                            )
             VALUES (
                     :NEW.sh_name,
                     NULL,
                     v_user,
                     v_date,
                     'Insert'
                    );
    ELSIF DELETING
    THEN
        INSERT INTO sh_audit(
                             new_name,
                             old_name,
                             user_name,
                             entry_date,
                             operation
                            )
             VALUES (
                     NULL,
                     :OLD.sh_name,
                     v_user,
                     v_date,
                     'Delete'
                    );
    ELSIF UPDATING
    THEN
        INSERT INTO sh_audit(
                             new_name,
                             old_name,
                             user_name,
                             entry_date,
                             operation
                            )
             VALUES (
                     :NEW.sh_name,
                     :OLD.sh_name,
                     v_user,
                     v_date,
                     'Update'
                    );
    END IF;
END;
/                          

触发器已编译并有效：

SQL> INSERT INTO superheoes(sh_name) VALUES ('Supe');
1 row created.

SQL> select * from sh_audit;
NEW_NAME             OLD_NAME             USER_NAME            ENTRY_DATE           OPERATION
-------------------- -------------------- -------------------- -------------------- --------------------
Supe                                      ALEK                 21/DIC/2016 15:25:37 Insert

我将检查表结构，模式，特权，...

Answer 2

尝试以下操作：它的工作。还要检查您是否具有execute特权。

  SQL> SELECT   * FROM   DBA_SYS_PRIVS where grantee = 'SYSUSR' and privilege = 'EXECUTE ANY PROCEDURE' ;
    GRANTEE                        PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    SYSUSR                       EXECUTE ANY PROCEDURE                    NO

触发器：

 CREATE OR REPLACE TRIGGER superheroes_audit
 BEFORE INSERT OR DELETE OR UPDATE
 ON superheoes
 FOR EACH ROW      
BEGIN
--   SELECT USER, TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS')
--     INTO v_user, v_date
--     FROM DUAL;
   IF INSERTING
   THEN
      INSERT INTO sh_audit (new_name,
                            old_name,
                            user_name,
                            entry_date,
                            operation)
           VALUES (:NEW.sh_name,
                   NULL,
                   USER,
                   TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'),
                   'Insert');
   ELSIF DELETING
   THEN
      INSERT INTO sh_audit (new_name,
                            old_name,
                            user_name,
                            entry_date,
                            operation)
           VALUES (NULL,
                   :OLD.sh_name,
                   User,
                   TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'),
                   'Delete');
   ELSIF UPDATING
   THEN
      INSERT INTO sh_audit (new_name,
                            old_name,
                            user_name,
                            entry_date,
                            operation)
           VALUES (:NEW.sh_name,
                   :OLD.sh_name,
                   User,
                    TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'),
                   'Update');
   END IF;
END;
/

有关触发功能，请参见演示：

表：

CREATE TABLE superheoes (sh_name VARCHAR2 (20));
CREATE  TABLE sh_audit (new_name  varchar2(20),
                            old_name varchar2(20),
                            user_name varchar2(30),
                            entry_date varchar2(30),
                            operation varchar2(20));

执行：

SQL> select *  from superheoes;
no rows selected
SQL> select * from sh_audit;
no rows selected
SQL> insert into superheoes values('XING');
1 row created.
SQL> commit;
SQL> select * from sh_audit;
NEW_NAME             OLD_NAME             USER_NAME
-------------------- -------------------- ------------------------------
ENTRY_DATE                     OPERATION
------------------------------ --------------------
XING                                      SYSUSR
21/DEC/2016 15:19:41           Insert
SQL> delete from superheoes;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from sh_audit;
NEW_NAME             OLD_NAME             USER_NAME
-------------------- -------------------- ------------------------------
ENTRY_DATE                     OPERATION
------------------------------ --------------------
XING                                      SYSUSR
21/DEC/2016 15:19:41           Insert
                     XING                 SYSUSR
21/DEC/2016 15:20:30           Delete