Create trigger Command
Triggers are the database operation that will be fired immediately when a DML statement (INSERT, UPDATE or DELETE) is executed on a database table.
Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
On table_name
[FOR EACH ROW]
WHEN (Condition)
BEGIN
---SQL Statement---
END
- Creating BEFORE INSERT Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2),
Create_datedate,
Created_by varchar2(10)
);
QueryCREATEOR REPLACETRIGGER Product_before_insert
BEFORE INSERT
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
-- Find username of person performing INSERT into table
SELECTuser INTO username
FROM dual;
-- Update create_date field to current system date
:new.create_date:= systemdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by:= username;
END;
- Creating BEFORE UPDATE Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2),
Updated_datedate,
Updated_by varchar2(10)
);
QueryCREATEOR REPLACETRIGGER Products_before_update
BEFORE UPDATE
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
--To search username of person performing UPDATE on the table
SELECTuser INTO username
FROM dual;
-- Update updated_date field to current system date
:new.updated_date:= systemdate;
-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by:= username;
END;
- Creating BEFORE DELETE Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2)
);
Query
CREATEOR REPLACETRIGGER Products_before_delete
BEFORE DELETE
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
SELECTuser INTO username
FROM dual;
INSERTINTO Products_audit
( Prod_ID,
Prod_Name,
Quantity,
Price,
delete_date,
deleted_by )
VALUES
(:old.Prod_ID,
:old.Prod_Name,
:old.Quantity,
:old.Price,
systemdate,
username);
END;
- Creating AFTER INSERT Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2)
);
QueryCREATEOR REPLACETRIGGER Products_after_insert
AFTERINSERT
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
SELECTuser INTO username
FROM dual;
INSERTINTO Products_audit
( Prod_ID,
Prod_Name,
Quantity,
Price,
username )
VALUES
(:new.Prod_ID,
:new.Prod_Name,
:new.Quantity,
:new.Price,
username);
END;
- Creating AFTER UPDATE Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2)
);
QueryCREATEOR REPLACETRIGGER Products_after_update
AFTERUPDATE
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
SELECTuser INTO username
FROM dual;
INSERTINTO Products_audit
( Prod_ID,
Price_before,
Price_after,
username )
VALUES
(:new.Prod_ID,
:old.Price,
:new.Price,
username);
END;
- Creating AFTER DELETE Trigger
Table
CREATETABLE Product
(
Prod_ID Number(10),
Prod_Name Varchar2(20),
Quantity Number(5),
Price number(5,2)
);
QueryCREATEOR REPLACETRIGGER Products_after_delete
AFTERDELETE
ON Products
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
SELECTuser INTO username
FROM dual;
INSERTINTO Products_audit
( Prod_ID,
Prod_Name,
Quantity,
Price,
delete_date,
deleted_by)
VALUES
(:old.Prod_ID,
:old.Prod_Name,
:old.Quantity,
:old.Price,
systemdate,
username);
END;
- Creating INSTEAD OF INSERT, UPDATE and DELETE Trigger
Syntax
CREATE [ OR REPLACE] TRIGGER trihgger_name
INSTEADOF
event1 [OR event2 OR event3]
ON view_name
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
trigger_body
TableCREATETABLE Emp AS
SELECT Emp_id, name, salary, depart_id, email, job_id
FROM Employees;
CREATETABLE depart AS
SELECT d,depart_id, d.depart_name, d. location_id,sum(e.salary) tot_dept_sal
FROM Employees e, department d
WHERE e.depart_id = d.depart_id
GROUPBY d.depart_id, d. depart_name, d. lpcation_id;
ViewCREATEVIEW Emp_details AS
SELECT e.EMP_id, e.name, e.salary, e.depart_id, e.email, e.job_id, d.depart_name,
d.location_id
FROM Employees e, department d
WHERE e.depart_id = d.depart_id;
ExampleCREATEOR REPLACETRIGGER NEW_Emp_depart
INSTEADOF INSERTOR UPDATEOR DELETEON Emp_details
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERTINTO new_emps INSERT
VALUES(:NEW.Emp_id,:NEW.name,:NEW.salary,
:NEW.depart_id,:NEW.email,:NEW.job_id, SYSDATE);
UPDATE depart UPDATE
SET tot_depart_sal = tot_depart_sal+ :NEW.salary
WHERE depart_id = :NEW. depart_id;
ELSEIF DELETING THEN
DELETEFROM EMP DELETE
WHERE Emp_id = :OLD.Emp_id;
UPDATE depart
SET tot_depart_sal = tot_depart_sal- :OLD.salary
WHERE depart_id = :OLD.depart_id
ENDIF;
END;
Previous article: Oracle Commands : Part 1
Next Article: Oracle Commands : Part 3
Join the conversation! Your thoughts help the community grow.