Study Notes
PL/SQL
Trigger
Triggers
PL/SQL Trigger
Triggers are stored program which are automatically executed or fired when any event occur.
Trigger occur in response to any of the following events :
A DML statement like (INSERT, UPDATE, and DELETE)
A DDL statement like (CREATE , ALTER or DROP)
A Database operation like (SERVER ERROR, LOGON, LOGOFF, STARTUP or
SHUTDOWN)
Triggers can be defined on a table, view, schema or a database.
Advantages of Triggers :
1. Generating some derived column values automatically
2. Enforcing referential integrity
3. Event logging and storing information on table access
4. Auditing
5. Synchronous replication of tables
6. Imposing security authorization
7. Preventing invalid transactions
Syntax for creating a Trigger :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
2
Triggers
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where,
Create or replace trigger is for creating a new trigger or replacing the existing trigger
with trigger name.
Before , After and Instead of are used to define when the trigger is going to be executed.
Instead Of is used for creating trigger on view.
Insert , Update, or delete defines the command on which the trigger has to be executed.
Of col_name specify thecolumn that has to be updated.
ON Table_name specify the table name which is associated with the trigger.
[REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old
values for various DML statements, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be
executed for each row being affected.
When the SQL statement is executed, which is called a table level trigger.
WHEN (condition) − This provides a condition for rows for which the trigger would
fire. This clause is valid only for row-level triggers.
3
Triggers
Consider the table of students
Select * from Student;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | FEES |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
Let us creates a row-level trigger for the Students table that would fire for INSERT or UPDATE
or DELETE operations performed on the STUDENT table. This trigger will display the Fees
difference between the old values and new values −
CREATE OR REPLACE TRIGGER display_Fees_changes
BEFORE DELETE OR INSERT OR UPDATE ON Student
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE
Fees_diff number;
BEGIN
Fees_diff := :NEW. Fees - :[Link] ;
dbms_output.put_line('Old Fees: ' || :[Link]);
dbms_output.put_line('New Fees: ' || :[Link]);
dbms_output.put_line(' Fees difference: ' || sal_diff);
END;
After executing this code output is :
Trigger created.
4
Triggers
Here OLD and NEW references are not available for table-level triggers, rather you can use them
for record-level triggers.
Triggering A Trigger :
INSERT INTO Students (ID,NAME,AGE,ADDRESS,FEES)
VALUES (7, 'Kriti', 22, 'Himanchal Pradesh', 7500.00 );
When this insert command is executed then it create display_Fees_changes trigger and fire it
and display the result as :
Old Fees:
New Fees: 7500
Fees difference:
This is a new record hence the old value is null here.
Let us issue an update command :
UPDATE customers
SET FEES = FEES + 500
WHERE id = 2;
When this update command is executed then it create display_Fees_changes trigger and fire it
and display the result as :
Old Fees: 1500
New Fees: 2000
Fees difference: 500
Drop a Trigger :
You can remove a trigger from the database by issuing the DROP TRIGGER statement.
ENABLE and DISABLE A Trigger :
A trigger can be in either of two distinct modes:
Enabled
5
Triggers
An enabled trigger executes its trigger body if a triggering statement is issued and the trigger
restriction, if any, evaluates to true. By default, triggers are enabled when first created.
Disabled
A disabled trigger does not execute its trigger body, even if a triggering statement is issued and
the trigger restriction (if any) evaluates to true.
Enabling trigger :
You enable a disabled trigger using the ALTER TRIGGER statement with the ENABLE option.
To enable the disabled trigger named display_Fees_changes on the Student table, enter the
following statement:
ALTER TABLE Student
ENABLE ALL TRIGGERS;
Disabling trigger :
Consider temporarily disabling a trigger if one of the following conditions is true:
• An object that the trigger references is not available.
• You must perform a large data load and want it to proceed quickly without firing triggers.
• You are loading data into the table to which the trigger applies.
You disable a trigger using the ALTER TRIGGER statement with the DISABLE option. To
disable the trigger display_Fees_changes on the Student table, enter the following
statement:
ALTER TABLE inventory
DISABLE ALL TRIGGERS;
LOG ON trigger :
These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after
successful authentication and before establishing the user session.
LOGON triggers are created at the server level and are useful below cases.
To audit login activity
To control the login activity