PL/SQL After Delete Trigger
In PL/SQL the AFTER DELETE
Trigger will fire after the DELETE operation is executed.
PL/SQL After Delete Trigger Syntax
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
AFTER DELETE ON table_name
[FOR EACH ROW]
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where:
CREATE TRIGGER trigger_name: To create trigger with sutable name.
REPLACE OR REPLACE TRIGGER trigger_name: To replace trigger with sutable name.
AFTER: To specifies when you want the trigger to be executed. The INSTEAD OF clause is used for creating trigger on a view.
DELETE: To specifies DML operation.
[ON table_name]: To specifies the name of the table associated with the trigger.
[FOR EACH ROW]: To specifies a row level trigger, Otherwise the trigger will execute just once when the SQL statement is executed.
WHEN (condition): The condition on which the trigger would fire. This is valid for row level triggers.
PL/SQL After Delete Trigger Example
To show how to create an AFTER DELETE trigger using the CREATE TRIGGER statement, use the following syntax:
CREATE OR REPLACE TRIGGER user_before_insert
AFTER DELETE
ON tblCustomerLoan
FOR EACH ROW
DECLARE
t_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO t_username
FROM dual;
-- Insert record into tblUser table
INSERT INTO tblUser
( firstName,
lastName,
address,
delete_date,
deleted_by )
VALUES
( :old.firstName,
:old.lastName,
:old.address,
sysdate,
t_username );
END;