Trigger

An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed. Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified. So it's add additional assistance to manage group of data base tables when ever insert, delete, update operation is performed. For example in banking normal business hours for transactions are from 9 am to 3 pm. suppose some one initiated the transaction after 3 pm. then we can have a mechanism to invoke a trigger by DBMS to raise application error: Transaction is not allowed. So it adds additional features in DBMS system to manage, monitor and control in our data base application. There are 12 kinds (3*2*2) of DML triggers for relational tables:

DML statements: Insert, update, delete

Timing: before , after

Level: Statement level, Row level

Examples:

Before row levels trigger:
1)
CREATE TRIGGER ins_trig BEFORE INSERT ON Emp
FOR EACH ROW
BEGIN
UPDATE Employee SET Salary=Salary-300 WHERE Perks>500;
END;
/
2)
CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
IF NEW.Salary<=500 THEN
SET NEW.Salary=10000;
ELSEIF NEW.Salary>500 THEN
SET NEW.Salary=15000;
END IF;
END
/

After statement level trigger:

CREATE OR REPLACE TRIGGER emp_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_action := 'Added employee(s)';
ELSIF UPDATING THEN
v_action := 'Updated employee(s)';
ELSIF DELETING THEN
v_action := 'Deleted employee(s)';
END IF;
INSERT INTO empauditlog VALUES (SYSDATE, USER,
v_action);
END;

DROP TRIGGER

The general syntax of DROP TRIGGER is :

DROP TRIGGER trigger_name