Total Pageviews

April 1, 2015

4/01/2015 02:42:00 AM


DATABASE TRIGGERS


Triggers are similar to procedures or functions in that they are named PL/SQL blocks with declarative, executable, and exception handling sections. A trigger is executed implicitly whenever the triggering event happens. The act of executing a trigger is known as firing the trigger.
RESTRICTIONS ON TRIGGERES
·         Like packages, triggers must be stored as stand-alone objects in the database and cannot be local to a block or package.
·         A trigger does not accept arguments.
USE OF TRIGGERS          
·         Maintaining complex integrity constraints not possible through declarative constraints enable at table creation.
·         Auditing information in a table by recording the changes made and who made them.
·         Automatically signaling other programs that action needs to take place when chages are made to a table.
·         Perform validation on changes being made to tables.
·         Automate maintenance of the database.
TYPES OF TRIGGERS
·         DML Triggers
·         Instead of Triggers
·         DDL Triggers
·         System Triggers
·         Suspend Triggers
CATEGORIES
Timing            --         Before or After
Level               --         Row or Statement
Row level trigger fires once for each row affected by the triggering statement. Row level trigger is identified by the FOR EACH ROW clause.
Statement level triggers fires once either before or after the statement.
DML TRIGGER SYNTAX
Create or replace trigger trigger_name
Before | after on insert or update or delete
[For each row]
Begin
            --null
End trigger_name
DML TRIGGERS
A DML trigger is fired on an INSERT, UPDATE, or DELETE operation on a database table. It can be fired either before or after the statement executes, and can be fired once per affected row, or once per statement.
The combination of these factors determines the types of the triggers. These are a total of 12 possible types (3 statements * 2 timing * 2 levels).
ORDER OF DML TRIGGER FIRING
·         Before statement level
·         Before row level
·         After row level
·         After statement level
Ex:
     Suppose we have a following table.
select * from student;
        NO NAME    MARKS
        ----- ------- ----------
         1      a         100
         2      b         200
         3      c         300
         4      d         400
Also we have triggering_firing_order table with firing_order as the field.
CREATE OR REPLACE TRIGGER TRIGGER1
   BEFORE INSERT
   ON student
BEGIN
   INSERT INTO trigger_firing_order
     VALUES   ('Before Statement Level');
END TRIGGER1;

CREATE OR REPLACE TRIGGER TRIGGER2
   BEFORE INSERT
   ON student
   FOR EACH ROW
BEGIN
   INSERT INTO trigger_firing_order
     VALUES   ('Before Row Level');
END TRIGGER2;
CREATE OR REPLACE TRIGGER TRIGGER3
   AFTER INSERT
   ON student
BEGIN
   INSERT INTO trigger_firing_order
     VALUES   ('After Statement Level');
END TRIGGER3;
CREATE OR REPLACE TRIGGER TRIGGER4
   AFTER INSERT
   ON student
   FOR EACH ROW
BEGIN
   INSERT INTO trigger_firing_order
     VALUES   ('After Row Level');
END TRIGGER4;
Output:
SELECT   * FROM trigger_firing_order;
no rows selected
INSERT INTO student
  VALUES   (5, 'e', 500);
1 row created.
SELECT   * FROM trigger_firing_order;
FIRING_ORDER
--------------------------------------------------
Before Statement Level
Before Row Level
After Row Level
After Statement Level

SQL select * from student;
        NO  NAME    MARKS
        ---- -------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         500

CORRELATION IDENTIFIERS IN ROW-LEVEL TRIGGERS
Inside the trigger, you can access the data in the row that is currently being processed. This is accomplished through two correlation identifiers - :old and :new.
A correlation identifier is a special kind of PL/SQL bind variable. The colon in front of each indicates that they are bind variables, in the sense of host variables used in embedded PL/SQL, and indicates that they are not regular PL/SQL variables. The PL/SQL compiler will treat them as records of type
 Triggering_table%ROWTYPE.
Although syntactically they are treated as records, in reality they are not. :old and :new are also known as pseudorecords, for this reason.
       Suppose we have a table called marks with fields no, old_marks, new_marks.
CREATE OR REPLACE TRIGGER OLD_NEW
   BEFORE INSERT OR UPDATE OR DELETE
   ON student
   FOR EACH ROW
BEGIN
   INSERT INTO marks
     VALUES   (:old.no, :old.marks, :new.marks);
END OLD_NEW;Output:
SQL select * from student;
        NO  NAME  MARKS
       ----- ------- ----------
         1        a         100
         2        b         200
         3        c         300
         4        d         400
         5        e         500
SQL select * from marks;
no rows selected
SQL insert into student values(6,'f',600);
1 row created.



SQL select * from student;
        NO  NAME   MARKS
       ---- -------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         500
         6       f         600
SQL select * from marks;
        NO  OLD_MARKS  NEW_MARKS
        ---- --------------- ---------------
                                            600

SQL update student set marks=555 where no=5;
1 row updated.

SQL select * from student;
        NO  NAME  MARKS
      ----- ------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         555
         6       f         600
SQL select * from marks;

  NO   OLD_MARKS   NEW_MARKS
 ------ ---------------- ---------------
                                        600
      5            500              555
SQL delete student where no = 2;
1 row deleted.
SQL select * from student;
        NO  NAME   MARKS
       ----  -------- ----------
         1        a         100
         3        c         300
         4        d         400
         5        e         555
         6        f         600
SQL select * from marks;
        NO  OLD_MARKS  NEW_MARKS
       -----  -------------- ----------------
                                            600
         5             500              555
         2             200
REFERENCING CLAUSE
If desired, you can use the REFERENCING clause to specify a different name for :old ane :new. This clause is found after the triggering event, before the WHEN clause.
Syntax:
        REFERENCING [old as old_name] [new as new_name]
Ex:
CREATE OR REPLACE TRIGGER REFERENCE_TRIGGER
   BEFORE INSERT OR UPDATE OR DELETE
   ON student
   REFERENCING OLD AS old_student NEW AS new_student
   FOR EACH ROW
BEGIN
   INSERT INTO marks
     VALUES   (:old_student.no, :old_student.marks, :new_student.marks);
END REFERENCE_TRIGGER; 
WHEN CLAUSE
WHEN clause is valid for row-level triggers only. If present, the trigger body will be executed only for those rows that meet the condition specified by the WHEN clause.
Syntax:
            WHEN trigger_condition;
Where trigger_condition is a Boolean expression. It will be evaluated for each row. The :new and :old records can be referenced inside trigger_condition as well, but like REFERENCING, the colon is not used there. The colon is only valid in the trigger body.
Ex:
CREATE OR REPLACE TRIGGER WHEN_TRIGGER
   BEFORE INSERT OR UPDATE OR DELETE
   ON student
   REFERENCING OLD AS old_student NEW AS new_student
   WHEN (:new_student.marks > 500)
BEGIN
   INSERT INTO marks
     VALUES   (:old_student.no, :old_student.marks, :new_student.marks);
END WHEN_TRIGGER;
TRIGGER PREDICATES
There are three Boolean functions that you can use to determine what the operation is.
The predicates are
INSERTING
UPDATING
DELETING
Ex:
CREATE OR REPLACE TRIGGER PREDICATE_TRIGGER
   BEFORE INSERT OR UPDATE OR DELETE
   ON student
BEGIN
   IF INSERTING
   THEN
      INSERT INTO predicates
        VALUES   ('I');
   ELSIF UPDATING
   THEN
      INSERT INTO predicates
        VALUES   ('U');
   ELSIF DELETING
   THEN
      INSERT INTO predicates
        VALUES   ('D');
   END IF;
END PREDICATE_TRIGGER;Output:
SQL delete student where no=1;

1 row deleted.

SQL select * from predicates;

MSG
---------------
D

SQL insert into student values(7,'g',700);

1 row created.
 
SQL select * from predicates;
MSG
---------------
D
I
SQL update student set marks = 777 where no=7;
1 row updated.
SQL select * from predicates;
MSG
---------------
D
I
U
INSTEAD-OF TRIGGERS
Instead-of triggers fire instead of a DML operation. Also, instead-of triggers can be defined only on views. Instead-of triggers are used in two cases:
·         To allow a view that would otherwise not be modifiable to be modified.
·         To modify the columns of a nested table column in a view.
SYSTEM TRIGGERS
System triggers will fire whenever database-wide event occurs. The following are the database event triggers. To create system trigger you need ADMINISTER DATABASE TRIGGER privilege.
STARTUP
SHUTDOWN
LOGON
LOGOFF
SERVERERROR
Syntax:
Create or replace trigger trigger_name
{Before | after}  {Database event} on {database | schema}
[When (…)]
[Declare]
            -- declaration section
Begin
            -- trigger body
[Exception]
            -- exception section
          End
Ex:
SQL create table user_logs(u_name varchar(10),log_time timestamp);

CREATE OR REPLACE TRIGGER AFTER_LOGON
     after logon on database
BEGIN
     insert into user_logs values(user,current_timestamp);
END AFTER_LOGON;
Output:

SQL select * from user_logs;
        no rows selected

SQL conn saketh/saketh

SQL select * from user_logs;
U_NAME     LOG_TIME
---------- ------------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM

conn system/oracle

SQL select * from user_logs;

U_NAME     LOG_TIME
---------- ------------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM
SYSTEM     22-JUL-07 12.07.34.218000 AM

conn scott/tiger

SQL select * from user_logs;

U_NAME     LOG_TIME
---------- -----------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM
SYSTEM     22-JUL-07 12.07.34.218000 AM
SCOTT      22-JUL-07 12.08.43.093000 AM
SERVERERROR
The SERVERERROR event can be used to track errors that occur in the database. The error code is available inside the trigger through the SERVER_ERROR attribute function.
Ex:SQL create table my_errors(error_msg varchar(200));


CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER
   AFTER SERVERERROR
   ON DATABASE
BEGIN
   INSERT INTO my_errors
     VALUES   (DBMS_UTILITY.format_error_stack);
END SERVER_ERROR_TRIGGER;
Output:
SQL create table ss (no));
create table ss (no))

ERROR at line 1:

ORA-00922: missing or invalid option

SQL select * from my_errors;

ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option

SQL insert into student values(1,2,3);

insert into student values(1,2,3)

ERROR at line 1:
ORA-00942: table or view does not exist

SQL select * from my_errors;

ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option
ORA-00942: table or view does not exist
SERVER_ERROR ATTRIBUTE FUNCTION

It takes a single number type of argument and returns the error at the position on the error stack indicated by the argument. The position 1 is the top of the stack.
Ex:

CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER
        after servererror on database
BEGIN
        insert into my_errors values(server_error(1));
END SERVER_ERROR_TRIGGER;

SUSPEND TRIGGERS
This will fire whenever a statement is suspended. This might occur as the result of a space issue such as exceeding an allocated table pace quota. This functionality can be used to address the problem and allow the operating to continue.
Syntax:
Create or replace trigger trigger_name
after  suspend on {database | schema}
[When (…)]
[Declare]
            -- declaration section
Begin
            -- trigger body
[Exception]
            -- exception section
          End <trigger_name>;
Ex:
    SQL CREATE TABLESPACE my_space DATAFILE 'f:\my_file.dbf' SIZE 2M;
    SQL CREATE TABLE student (sno  NUMBER (2), sname VARCHAR (10))
TABLESPACE my_space;

CREATE OR REPLACE TRIGGER SUSPEND_TRIGGER
      after suspend on database
BEGIN
      dbms_output.put_line(‘ No room to insert in your tablespace');
END SUSPEND_TRIGGER;
Output:
        Insert more rows in student table then , you will get
        No room to insert in your tablespace

 
Related Posts Plugin for WordPress, Blogger...