How to Enable, Generate, Update, Delete & Insert Statements for the Oracle Database
The Oracle database is an object-relational database management system consisting of an application and data storage. A trigger is a named program stored in an Oracle database and executed for a specific event. The event can be a database manipulation, a definition or operation associated with a table, or a view, schema or database. Triggers are enabled by default in Oracle. Examples of triggers associated with database manipulation include DELETE, INSERT and UPDATE statements.
Other People Are Reading
Instructions
-
-
1
Ensure you have the correct permissions to use triggers. You must have the CREATE TRIGGER system privilege as well as permission to alter tables named in the triggering statement. If you are in doubt about your privileges, contact your system administrator.
-
2
Insert a CREATE TRIGGER statement into your code. For example, the following code executes when a database management operation is performed on the table:
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :OLD.sal);
dbms_output.put(' New salary: ' || :NEW.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
/ -
-
3
Modify the code to represent when you want the trigger to fire. The BEFORE statement indicates the trigger should execute before the table is accessed. The statement can be modified to AFTER if you want the trigger to change or query the same table.
-
4
Change the code to reflect whether you want the trigger to fire once or multiple times. In this example, FOR EACH ROW indicates that you want the trigger to fire once for every row in the table. If you delete this line of code, the trigger will fire once for each statement.
-
5
Modify the WHEN clause to indicate when the trigger body executes on a row. In this example, WHEN (NEW.EMPNO > 0) indicates that the trigger body will only run if EMPNO is positive.
-
1
Tips & Warnings
If you want to disable a trigger, use an ALTER TRIGGER statement with a DISABLE clause. For example, to disable triggers associated with an income table, insert the following line of code:
ALTER TABLE Income DISABLE ALL TRIGGERS;
