BEFORE INSERT Trigger
A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax for an BEFORE INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name BEFORE INSERT ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
trigger_name is the name of the trigger to create.
Restrictions
- You can not create a BEFORE trigger on a view.
- You can update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:
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) );
We could then create a BEFORE INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_insert BEFORE INSERT ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing INSERT into table SELECT user INTO v_username FROM dual; -- Update create_date field to current system date :new.create_date := sysdate; -- Update created_by field to the username of the person performing the INSERT :new.created_by := v_username; END;
AFTER INSERT Trigger
The syntax for an AFTER INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name AFTER INSERT ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;trigger_name is the name of the trigger to create.
Restrictions
- You can not create an AFTER trigger on a view.
- You can not update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );We could then create an AFTER INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
END;
BEFORE UPDATE Trigger
The syntax for an BEFORE UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name BEFORE UPDATE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;trigger_name is the name of the trigger to create.
Restrictions
- You can not create a BEFORE trigger on a view.
- You can update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2), updated_date date, updated_by varchar2(10) );We could then create a BEFORE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_update BEFORE UPDATE ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing UPDATE on the table SELECT user INTO v_username FROM dual; -- Update updated_date field to current system date :new.updated_date := sysdate; -- Update updated_by field to the username of the person performing the UPDATE :new.updated_by := v_username; END;
AFTER UPDATE Trigger
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name AFTER UPDATE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;trigger_name is the name of the trigger to create.
Restrictions
- You can not create an AFTER trigger on a view.
- You can not update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );We could then create an AFTER UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;
BEFORE DELETE Trigger
The syntax for an BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name BEFORE DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;trigger_name is the name of the trigger to create.
Restrictions
- You can not create a BEFORE trigger on a view.
- You can update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );We could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by )
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;
AFTER DELETE Trigger
The syntax for an AFTER DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name AFTER DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;trigger_name is the name of the trigger to create.
Restrictions
- You can not create an AFTER trigger on a view.
- You can not update the :NEW values.
- You can not update the :OLD values.
For Example
If you had a table created as follows:CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );We could then create an DELETE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by)
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;
Drop a Trigger
DROP TRIGGER trigger_name;
For Example
If you had a trigger called orders_before_insert, you could drop it with the following command:DROP TRIGGER orders_before_insert;
Disable a Trigger
ALTER TRIGGER trigger_name DISABLE;
For Example
If you had a trigger called orders_before_insert, you could disable it with the following command:ALTER TRIGGER orders_before_insert DISABLE;
Enable a Trigger
ALTER TRIGGER trigger_name ENABLE;
For Example
If you had a trigger called orders_before_insert, you could enable it with the following command:ALTER TRIGGER orders_before_insert ENABLE;
Enable all Triggers on a table
ALTER TABLE table_name ENABLE ALL TRIGGERS;
For Example
If you had a table called orders and you wanted to enable all triggers on this table, you could execute the following command:ALTER TABLE orders ENABLE ALL TRIGGERS;
Disable all Triggers on a table
ALTER TABLE table_name DISABLE ALL TRIGGERS;
For Example
If you had a table called orders and you wanted to disable all triggers on this table, you could execute the following command:ALTER TABLE orders DISABLE ALL TRIGGERS;
