Scribd
Upload
7 views

PL-SQL Triggers

The document discusses triggers in PL/SQL programs. It defines triggers, describes the different types of triggers and their uses. It also provides the syntax for creating triggers and an example trigger that logs salary changes in an employee table.

Uploaded by

walterharveldwhite
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views

PL-SQL Triggers

The document discusses triggers in PL/SQL programs. It defines triggers, describes the different types of triggers and their uses. It also provides the syntax for creating triggers and an example trigger that logs salary changes in an employee table.

Uploaded by

walterharveldwhite
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

PL/SQL programs using Triggers

AIM
To study and implement the concepts of triggers.

DEFINITION
 A trigger is a statement that is executed automatically by the system as a side effect of a
modification to the database. The parts of a trigger are,
 Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies
the table to which the trigger is associated.
 Trigger body or trigger action: It is a PL/SQL block that is executed when the triggering
statement is used.
 Trigger restriction: Restrictions on the trigger can be achieved

The different uses of triggers are as follows,


 To generate data automatically
 To enforce complex integrity constraints
 To customize complex securing authorizations
 To maintain the replicate table
 To audit data modifications

TYPES OF TRIGGERS
 The various types of triggers are as follows,
 Before: It fires the trigger before executing the trigger statement.
 After: It fires the trigger after executing the trigger statement.
 For each row: It specifies that the trigger fires once per row.
 For each statement: This is the default trigger that is invoked. It specifies that the trigger fires
once per statement.

VARIABLES USED IN TRIGGERS

 :new
 :old
These two variables retain the new and old values of the column updated in the database. The
values in these variables can be used in the database triggers for data manipulation

TRIGGERS - SYNTAX

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)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing


trigger with the trigger_name.
 {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed.
The INSTEAD OF clause is used for creating trigger on a view.
 {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
 [OF col_name] − This specifies the column name that will be updated.
 [ON table_name] − This specifies the name of the table 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. Otherwise the trigger will execute just once 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.

Example (consider a table employee)


CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON employee


FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary - :OLD.salary;

dbms_output.put_line('Old salary: ' || :OLD.salary);

dbms_output.put_line('New salary: ' || :NEW.salary);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

Output:
(OLD and NEW references are not available for table-level triggers, rather you can use them for
record-level triggers.)

When a record is updated in the employee table, the above create trigger,
display_salary_changes will be fired and it will display the following result −

Old salary: 1500


New salary: 2000
Salary difference: 500

Result:
Thus the PL/SQL program on Triggers was executed successfully

You might also like