To the point guide to PL/SQL

What is a Trigger?

In PL/SQL trigger is a structure block stored into database invokes automatically whenever a specified event occurs. Events are any of the following:

• Database Definition (DDL): CREATES, ALTER, and DROP.
• Database Manipulation Language (DML): DELETE, INSERT, or UPDATE.
• Database operation: LOGON, SERVERERROR, LOGOFF, SHUTDOWN/ STARTUP.
• Whenever user neet to validate DML statements, to modify a table by using triggers on the table that gets invoked automatically whenever DML statement is executed.
• To avoid invalid transaction
• Triggers Enforces referential integrity
• Triggers also enforce constraints such as any insert/ delete /update statements should not be allowed on a particular table.
• Triggers helps in auditing
• Triggers support for security authorization.

Advantages of Triggers:

Creating Triggers:

Defining a trigger by using CREATE TRIGGER statement:

CREATE [OR REPLACE] TRIGGER trigger_name		
	BEFORE | AFTER
	[INSERT, UPDATE, DELETE [COLUMN NAME..] ON table_name

	Referencing [ OLD AS OLD | NEW AS NEW ]
	FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]

DECLARE
    Declaration part;

BEGIN
    Executable part;
		
EXCEPTION
    Exception part;
		
END;

 Let's check some important details for above syntax

• CREATE [OR REPLACE] TRIGGER trigger_name: Creates trigger with the trigger_name or replaces an existing trigger.
• {BEFORE | AFTER | INSTEAD OF}: This specifies when the trigger would be executed.
• {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
• [ON table_name]: This specifies the table name.
• [REFERENCING OLD AS o NEW AS n]: This allows referring new and old values for various DML statements.
• [FOR EACH ROW]: This specifies a row level trigger that is the trigger would be executed for each row being affected.
• WHEN (condition): This provides a condition for rows for which the trigger would fire.
• OLD and NEW references can be used for record level triggers and these are not available for table level triggers.
• The AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Example:

CREATE TABLE Stud_Example
( Stud_id number(5),
  marks number(4),
  current_date date,
  created_by varchar2(10)
);

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
   ON Stud_Example
   FOR EACH ROW
   
DECLARE
   sname varchar2(20);
   
BEGIN

   -- Find student name of person performing INSERT into table
   SELECT name INTO sname FROM dual;
   
   -- Update current_date field to current system date
   :new.current_date := sysdate;
   
   -- Update created_by field to the username of the person performing the INSERT
   :new.created_by := sname;
   
END;

Result:

Trigger created.

Restriction to Deleting Trigger:

To avoid deleting row:

Example:

CREATE or REPLACE TRIGGER triggr1
	AFTER
	DELETE ON Stud_Details
	FOR EACH ROW
BEGIN
      IF :old.Stud_id = 1 THEN
      raise_application_error(-20015, 'This row can’t be deleted!');
      END IF;
END;
/

Result:

SQL>delete from Stud_Details where Stud_id = 1;
Error Code: 20015
Error Name: This row can’t be deleted!