Enable and Disable Triggers

In Oracle, it is possible to enable or disable triggers by using the ALTER TRIGGER and ALTER TABLE commands.

• ALTER TRIGGER DISABLE;


• ALTER TRIGGER ENABLE;


• ALTER TABLE DISABLE ALL TRIGGERS;


• ALTER TABLE ENABLE ALL TRIGGERS;

Now n Oracle 11g, you can enable and disable the triggers at the time of creating. Mean we can create trigger in enabled or disabled state. By default it is in enabled state.

CREATE TABLE trigger_test (
id NUMBER,
description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_test_trg
BEFORE INSERT ON trigger_test
FOR EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_TEST_TRG will be Executed');
END;
/

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_test VALUES (1, 'Enabled');
TRIGGER_TEST_TRG will be Executed

1 row created.

SQL>

CREATE OR REPLACE TRIGGER trigger_test_trg
BEFORE INSERT ON trigger_test
FOR EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_TEST_TRG will be Executed');
END;
/

SQL> INSERT INTO trigger_test VALUES (2, 'Disabled');

1 row created.

SQL>

Execution Order of Triggers

You can create more than one trigger for the same timing point, at the same time we are not sure which trigge will execute first. To over come this confusion, Oracle 11g has provide the update trigger syntax, which includes FOLLOWS clause. The FOLLOWs clause guarantees the execution order of thye triggers created for the same timing point.

Example:


CREATE TABLE trigger_Execution_Order (
id NUMBER,
description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_1
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(trigger_Execution_Order_TRG_1 - Executed');
END;
/

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_2
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('trigger_Execution_Order_TRG_2 - Executed');
END;
/

Now, if we insert a record into the trigger_Execution_Order

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_Execution_Order VALUES (1, 'First Trigger will execute');
TRIGGER_EXECUTION_ORDER_TRG_1 - Executed
TRIGGER_EXECUTION_ORDER_TRG_2 - Executed

1 row created.

SQL>

By using the FOLLOWS clause, we can specify that the TRIGGER_EXECUTION_ORDER_TRG_2 trigger should be executed before the TRIGGER_EXECUTION_ORDER_TRG_1 trigger by recreating the TRIGGER_EXECUTION_ORDER_TRG_1 trigger.

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_1
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
FOLLOWS trigger_Execution_Order_trg_2
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_EXECUTION_ORDER_TRG_1 - Executed');
END;
/

Now the trigger TRIGGER_EXECUTION_ORDER_TRG_1 will always follows the trigger TRIGGER_EXECUTION_ORDER_TRG_2.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_Execution_Order VALUES (2, 'Second Trigger will execute');
TRIGGER_EXECUTION_ORDER_TRG_2 - Executed
TRIGGER_EXECUTION_ORDER_TRG_1 - Executed

1 row created.

SQL>

INSTEAD OF Triggers

INSTEAD OFtriggers are defined on Views rather than on tables. Such type of triggers can be used to remove the restrictions on the Views that cannot be updated. These triggers provide a transparent way of modifying Views that cannot be modified directly by issuing a DML statement INSERT, UPDATE, or DELETE. These triggers are called the INSTEAD OF triggers because apart from other triggers, Oracle fires these triggers instead of executing the triggering statement.

Whenever you issue an INSERT, UPDATE, or DELETE statement against a View, the INSTEAD OF trigger will be fired to update the underlying tables. The INSTEAD OF triggers are activated for each row of the View that gets modified.

Restrictions and Limitations

• The INSTEAD OF trigger can be defined only on Views, and the Views cannot be distributed across multiple systems.
• For any given View, only one INSTEAD OF trigger can be defined for each of these operations: INSERT, UPDATE, and DELETE. Therefore, a View can have maximum of three INSTEAD OF triggers.
• The target View of the INSTEAD OF trigger must be based on a table. It cannot be based on another View, a derived table, or multiple tables.
• The INSTEAD OF trigger cannot be created on Views for which WITH CHECK OPTION is specified.

Example:

Create a View "Employee_Info" for displaying information about employees and their departments:

CREATE or REPLACE VIEW Employee_info AS
SELECT Emp.EmpNo, Emp.Ename, Emp.Sal, Emp.MGR, Dept.DeptNo,
Dept.Dname, Dept.LOC FROM Emp, Dept
WHERE Emp.DeptNo = Dept.DeptNo;

Then open Notepad or SQL *Plus and enter following code in to it.

CREATE OR REPLACE TRIGGER Employee_info_insert
INSTEAD OF INSERT ON Employee_Info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO Dept(Deptno, Dname, LOC)
VALUES ( :NEW.Deptno, :NEW.Dname, :NEW.LOC);
INSERT INTO Emp(Empno, Ename, Sal, MGR, Deptno)
VALUES ( :NEW.Empno, :NEW.Ename, :NEW.Sal, :NEW.MGR,
:NEW.Deptno);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (-20107, ‘Duplicate Employee or
Department NO.’);
END Employee_info_insert;
/

Execute the above code to create an INSTEAD OF trigger.

Now, you can insert records into both the tables through the View (as long as all NOT NULL columns receive values). Enter the following statement in SQL*Plus to insert data into the Employee and Department tables through the Employee_Info View:

INSERT INTO Employee_Info VALUES(7999, ‘MILLER’, 1500, 7782, 50, ‘RND’,
‘NEW YORK’);

After executing the INSERT statement, the trigger will fire and the records will be inserted into both the table Employee and Department.

Compound Triggers

In the earlier versions of Oracle, a user had to create separate triggers for each of the following timing points:

1. Before the trigger statement
2. After the trigger statement
3. Before each row changed by the trigger statement
4. After each row changed by the trigger statement

However in Oracle 11g, you can create a single trigger body for all timing points to share the common data. The compound triggers can act as statement level triggers as well as row level triggers. To use the compound triggers, you need to use package variables so that the data can be shared. In the compound triggers, there is a separate local declaration area for each timing point section. A compound trigger does not support the Exception section, but you can implement the Exception section for each timing point section.

The syntax for using the compound triggers is as follows:

CREATE OR REPLACE TRIGGER
FOR ON
COMPOUND TRIGGER
g_global_variable VARCHAR2(10); --Global declaration
BEFORE STATEMENT IS
BEGIN
statements;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
statements;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
statements;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
statements;
END AFTER STATEMENT;
END ;
/

The following example will illustrate how to create a compound trigger.

1. Enter the following statement in SQL *Plus to create the table EmpDemo:

CREATE TABLE EmpDemo as SELECT Empno, Ename, Sal, Hiredate
FROM Emp;

The above statement will create the table EmpDemo from the existing table Emp with the columns Empno, Ename, Sal, and Hiredate.

2. Next, enter the following statement in SQL *Plus to create another table with the name EmpAudit.

CREATE TABLE EmpAudit
(
Empno NUMBER(4),
ModOnDate DATE,
ModbyUser VARCHAR2(30),
old_Hiredate DATE,
new_Hiredate DATE
);

4. Next, enter the following PL/SQL code into the Notepad editor and then save the file at C:\oracle_11g\c08_oracle_11g with the file name tr_Emp_track.txt. Alternatively, enter the following code directly in SQL * Plus:

CREATE OR REPLACE trigger tr_Emp_track
FOR UPDATE OF Hiredate
ON EmpDemo
COMPOUND TRIGGER
TYPE ty_EMP_DETAILS IS TABLE OF EmpAudit%ROWTYPE
INDEX BY PLS_INTEGER;
Tab_Emp_Details ty_EMP_DETAILS;
ctr pls_integer := 0;
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before statement');
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before each row');
END BEFORE EACH ROW;
AFTER EACH ROW IS
8-66 Learning Oracle 11g: A PL/SQL Approach
BEGIN
ctr := ctr + 1;
DBMS_OUTPUT.PUT_LINE('After each row. Empno='||:new.Empno);
Tab_Emp_Details(ctr).Empno := :new.Empno;
Tab_Emp_Details(ctr).ModOnDate := sysdate;
Tab_Emp_Details(ctr).ModbyUser := user;
Tab_Emp_Details(ctr).old_Hiredate := :old.Hiredate;
Tab_Emp_Details(ctr).new_Hiredate := :new.Hiredate;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After statement');
FORALL counter IN 1..Tab_Emp_Details.count()
INSERT INTO EmpAudit
VALUES Tab_Emp_Details(counter);
END AFTER STATEMENT;
END tr_Emp_track;

3. Now, execute the contents of the file tr_Emp_track.txt to create the trigger tr_Emp_track. To do so, enter the following command in SQL *Plus:

SQL>@ ‘C:\oracle_11g\c08_oracle_11g\tr_Emp_track.txt’

4. After creating the trigger tr_Emp_track, enter the following statement to update the record of the EmpDemo table:

UPDATE EmpDemo SET Hiredate = SYSDATE WHERE Empno = 7499;

After executing the above UPDATE statement, the trigger tr_Emp_track will be fired and it will insert a row in the EmpAudit table. The output of the above statement is as follows:

Before statement
Before each row
After each row. Empno = 7499
After statement
1 row updated.

5. Now, enter the following statement to check whether or not the record has been inserted

into the table EmpAudit.
SELECT * FROM EmpAudit;

The output of the above statement is as follows:

EMPNO MODONDATE MODBYUSER OLD_HIREDATE NEW_HIREDATE
----------- --------------------- -------------------- ------------------------ --------------------------
7499 13-OCT-09 SCOTT 20-FEB-81 13-OCT-09

System-Level Event Triggers

You can create triggers such that they will fire when certain system-level events are raised. System-level events, also called as database events, are associated with entire database instance or schema and not with individual table or row. The system-level events can be defined at the database level or schema level.

The system-level events include the following:

1. Database STARTUP and SHUTDOWN
2. Server error message events (SERVERERROR)

The startup and shutdown events are associated with the database instance. The server error message events, on the other hand can be associated with either the database instance or with a particular schema.

The attributes of these triggers include system event, instance number, database name, and error number.

STARTUP Triggers
The STARTUP triggers fire immediately after the database is opened by an instance.
The following example will show how to create the STARTUP trigger:

CREATE OR REPLACE TRIGGER Trig_Startup
AFTER STARTUP
Cursors and Triggers 8-59
ON DATABASE
BEGIN
DBMS_OUTPUT.ENABLE;
END;
/

SHUTDOWN trigger
The SHUTDOWN trigger is fired when an instance of the server starts shutting down.
You can use these triggers to shut down the instance applications completely, when the database shuts down.

The following example will show how to create the SHUTDOWN Trigger:

Enter the following statement to create the table error_info:

CREATE TABLE SHUTDOWN_Info(
User_name VARCHAR2(30),
Logon_date DATE);

After creating the table, the next step is to create a system-level SHUTDOWN trigger that will fill information in the table SHUTDOWN_Info at the time you log on.

The following code will create the SHUTDOWN trigger:

CREATE OR REPLACE TRIGGER Trig_Startup
AFTER SHUTDOWN
ON DATABASE
DECLARE
user_name VARCHAR2(20);
sys_date DATE;
BEGIN
SELECT user, SYSDATE INTO user_name, sys_date
FROM Dual;
INSERT INTO SHUTDOWN_Info VALUES
(user_name, sys_date);
COMMIT;
END;
/

TRIGGER

A trigger is a special class of stored procedure and is defined on tables or views. Triggers are executed automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or a View.

A trigger cannot be called or executed. Triggers are fired automatically by the database.

The database triggers are used for the following purpose:

1. To generate the resulting data automatically.
2. To enforce complex integrity constraints, security authorizations, and business rules.
3. To avoid invalid transactions.
4. To enforce referential integrity across nodes in a distributed database.
5. To provide transparent event logging and advanced auditing data modifications.
6. To maintain synchronous table replicates and check the status of the table access.

Triggers are categorized in the following three ways:

1. Row Level Triggers
2. Statement Level Triggers
3. Before and After Triggers

Row Level Trigger
The row level trigger specifies that a trigger will be fired each time a row in a table is affected by the trigger statement.

Statement Level Trigger
The statement level trigger specifies that a trigger will be fired on behalf of the trigger statement. The statement level trigger does not depend on the number of rows affected.

Before and After Triggers
While defining a trigger, you can specify whether to perform a trigger action (or execute trigger body) before or after the triggering statement.

Trigger Syntax
The syntax for creating a trigger is as follows:

CREATE OR REPLACE TRIGGER [schema.]trigger_name
{ BEFORE, AFTER }
{ DELETE, INSERT, UPDATE [ OF column, .... ] }
ON [schema.]table_name
[ REFERENCING {OLD [AS] old}
{NEW [AS] new} ]
[ FOR EACH ROW [ WHEN search_condition ]
DECLARE
-- Declarative section: variables, types, and local subprograms
BEGIN
-- Executable section: procedural and SQL statements
-- This is the only section of the block required
EXCEPTION
-- Exception handling section: error handling statements.
END;

Example: The following steps are required to create the BEFORE INSERT trigger:

1. Enter the following statement in SQL*Plus to create the table Orders:

CREATE TABLE Orders
(
order_id NUMBER(5),
quantity NUMBER(4),
cost_per_item NUMBER(6,2),
total_cost NUMBER(8,2),
create_date DATE,
created_by VARCHAR2(10)
);

2. Open an editor (Notepad), enter the following code to create the BEFORE INSERT
trigger, and then save the file at C:\oracle_11g\c08_oracle_11g with file name
orders_before_insert.txt. Alternatively, enter the following code directly in SQL*Plus:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON Orders
FOR EACH ROW
DECLARE
v_username VARCHAR2(10);
v_Date DATE;
BEGIN
SELECT User, SYSDATE INTO v_username, v_Date FROM dual;
:NEW.create_date := v_Date;
:NEW.created_by := v_username;
END;
/

In the BEGIN section, the correlation name NEW is used to access and assign the values to the columns create_date and created_by of the table Orders.

3. Now, execute the contents of file to create the trigger. To do so, enter the following command in SQL *Plus:

SQL>@ ‘C:\oracle_11g\c08_oracle_11g\orders_before_insert.txt’

After executing the order_before_insert trigger, it will be saved to the Oracle database and will be fired before the trigger statement is issued.

4. Next, enter the following statement to insert a row into the Orders table.

INSERT INTO Orders VALUES(1, 20, 5, 100, '13-Oct-09', ‘Robert’);

Again, enter the following statement to check the data of the Orders tables.

SELECT * FROM Orders;