Trigger creation traps

Trigger creation traps

The creation of Oracle triggers differs from the creation of stored procedures, functions, and packages.

Trap 1:

The current schema is inconsistent with the trigger owner.

For example:

The current login schema is R5_T150, and then execute the following create trigger:

create or replace trigger "R5_T135".TRI_forbidden_01  before update or delete of GOODSCODE on t_goodsbegin        RAISE_APPLICATION_ERROR(-20002, 'forbidden operations!');end TRI_forbidden_01;

If it is a stored procedure or function, it is no problem to create such a function, but the trigger is different. Why?

Note that triggers can be cross-schema (system-level triggers ). therefore, the trigger created in the code above should belong to the schema R5_T135 (which is a DBA role), but the table triggered is R5_T150.

In this way, a strange phenomenon occurs in the PL/SQL DEV tool. In the current SCHEMA, you can view the trigger on the table and find "R5_T135 ". TRI_FORBIDDEN_01, but all triggers in the current SCHEMA cannot be found.

Trap 2:

If we use the SQL Window in PL/SQL DEV to create a trigger (or use the following code to create it using the sqlplus script ):

create or replace trigger  TRI_forbidden_01  before update or delete of GOODSCODE on t_goodsbegin        RAISE_APPLICATION_ERROR(-20002, 'forbidden operations!');end TRI_forbidden_01;alter trigger TRI_forbidden_01 enable;

In this way, the created trigger is displayed as an invalid compilation status. Check its source code and find that the last alter trigger TRI_forbidden_01 enable statement is also in the source code of the trigger.

Finally, do not create trigger "TRI_aaBB" and "TrI_Test" with the same name as this, because some tools will report errors when deleting or compiling such triggers, and you can only use commands to operate them, quite troublesome.