1.

How to declare a constant in PL/SQL?

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).

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