1.

What are Triggers in PL/SQL? When do we use them?

Answer»

Triggers are stored in a database and fired when some event OCCURS. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.

Syntax

The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:

CREATE [OR REPLACE ] TRIGGER name_of_trigger   {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;

Here,

  • CREATE [OR REPLACE] TRIGGER name_of_trigger – The statement creates or REPLACES existing trigger with the name_of_trigger.
  • {BEFORE | AFTER | INSTEAD OF} – The BEFORE or AFTER specifies when the trigger will get executed. The INSTEAD OF is for CREATING trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} – These clauses specify the DML operation.
  • [OF col_name] − The column name that will be updated.
  • [ON table_name] – The name of the table associated with the trigger.
  • [REFERENCING OLD AS o NEW AS n] − Refer new and old values for various DML statements.
  • [FOR EACH ROW] − A row-level trigger, i.e. the trigger will be executed for each row being affected.
  • WHEN (condition) − A condition for rows for which the trigger would fire.

Let us now take an example wherein we will create a trigger, trigger it, and even delete it.

For that, we are considering the following table <DEPTBUDGET>:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 56678      | |  2 | Marketing     | EAST |      87687 | |  3 | Operations   | WEST |      98979 | |  4 | Technical      | SOUTH |    76878 | |  5 | Accounting   | NORTHWEST| 86767      | +----------+------------------+------------------+--------------+

Create Trigger

Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget DIFFERENCES between new and old assigned budgets:

CREATE OR REPLACE TRIGGER budget_diff BEFORE DELETE OR INSERT OR UPDATE ON deptbudget FOR EACH ROW WHEN (NEW.ID > 0) DECLARE   budget_diff number; BEGIN   budget_diff := :NEW.budget  - :OLD.budget;   dbms_output.put_line('Old Budget Assigned =' || :OLD.budget);   dbms_output.put_line('New Budget Assigned = ' || :NEW.budget);   dbms_output.put_line('Differences in Budget = ' || budget); END; /

The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above: 

BEFORE DELETE OR INSERT OR UPDATE ON deptbudget

We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.

The statement would update an existing record in the table:

UPDATE budget SET  = budget + 4500 WHERE id = 4;

We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following: 

Old Budget Assigned = 76878 New Budget Assigned = 81378 Differences in Budget = 4500


Discussion

No Comment Found

Related InterviewSolutions