الدرس الثاني عشر

CREATE or REPLACE TRIGGER trigger_name
TRIGGER TYPE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

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;

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) 
);

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- ايجاد اسم المستخدم
SELECT user INTO v_username
FROM dual;
-- تعديل تاريخ التعديل حيث new ترمز الى قيمة جديدة
:new.create_date := sysdate;
-- هنا نضيف اسم المستخدم الجديد حيث استخدمنا new لنضع القيمة الجديدة وهو عبارة  باراميتر
INSERT
:new.created_by := v_username;
END;

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;

CREATE TABLE orders 
( order_id number(5), 
quantity number(4), 
cost_per_item number(6,2), 
total_cost number(8,2) 
);

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

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;

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 TRIGGER trigger_name;

DROP TRIGGER orders_before_insert;

ALTER TRIGGER trigger_name DISABLE;

ALTER TRIGGER orders_before_insert DISABLE;

ALTER TABLE table_name DISABLE ALL TRIGGERS;

ALTER TABLE orders DISABLE ALL TRIGGERS;

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER orders_before_insert ENABLE;

ALTER TABLE table_name ENABLE ALL TRIGGERS;

ALTER TABLE orders ENABLE ALL TRIGGERS;