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
An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed. 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
A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed. 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
An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed. 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
A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed. 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
An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed. 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;
A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
