Thursday, August 4, 2016

Database Triggers in Oracle 11g

Database Triggers in Oracle 11g Examples

Database trigger

A set of commands that are triggered to execute (e.g., log in, modify a row in a table, execute a DDL statement) when an event occurs in the database.

Triggers are named PL/SQL blocks with declarative, executable, and exception handling sections
    • A trigger is executed implicitly whenever the triggering event takes place
    • Triggers do not accept arguments
    • Triggers are stored database objects.
    • Like Packages there is no concept of local triggers in a block

Stored Procedures vs Database Triggers

  1. A procedure is called explicitly by a user, application or a trigger.A trigger executes implicitly in response to an event, such as an update operation on a table
  2. Procedure Cannot be inactive, We can enable and disable Triggers according to the need
  3. Procedure Can have parameters, Triggers Cannot have parameters
  4. No restriction on the size of a procedure,There may be a restriction on the size of a trigger depending on the version of oracle used. Mostly 32KB

Triggers Application:
  • To impose complex integrity constraints not possible through declarative constraints
  • To audit information in a table
  • To create replica of a table etc.
  • Security reasons
Purpose of triggers
Triggers can be written for the following purposes:
  • Generating some derived column values automatically
  • Enforcing referential integrity
  • Event logging and storing information on table access
  • Auditing
  • Synchronous replication of tables
  • Imposing security authorizations
  • Preventing invalid transactions

Triggers can enforce complex integrity rules.
Example: A trigger can ensure that  employee's new salary is within the job classification's limits. This cannot be done with constraints.
Triggers can be used to monitor and store the history of changes made to a table.
Example: A trigger can track the changes made to the EMP table, which row/s was inserted, updated, deleted, when the operation was performed and which user made this change. All this information can be stored in a table.
Triggers can be used to create a replica  of tables synchronously, in real time.
Example: A trigger can maintain at location X, a replica of EMP table that is physically located on a server at location Y.
Triggers are commonly used to enforce complex security authorizations for table data that cannot be defined using the database security features provided by Oracle.
Example: A trigger that prohibits updates to salary data of the EMP table during weekends, holidays, and non-working hours.

¢DML Trigger Components
Statement Triggers
¢The trigger body executes only once for the triggering event. This is the default.
¢Syntax:
CREATE  [ OR REPLACE ]  TRIGGER  trigger_name
trigger_timing  event1  [ OR  event2  OR event3 ]
ON  table_name
PL/SQL Block;
Statement Triggers: Example
¢The Trigger will be fired  and will disallow on Saturday or Sunday Or after office hours( 8am to 6pm) any DML operation on the EMP table.When a database trigger fails, the triggering statement is automatically rolled back by the Oracle Server.
 CREATE  OR  REPLACE  TRIGGER  chk_time
  BEFORE  INSERT  OR  UPDATE  OR  DELETE
  ON  emp
  BEGIN
  dbms_output.put_line('hi');
     IF  (TO_CHAR(sysdate,'DY')  IN  ('SAT','SUN'))   
          OR (TO_CHAR(sysdate,'HH24')  NOT 
           BETWEEN  '08' AND '18')
  THEN
           RAISE_APPLICATION_ERROR(-20400, '*****YOU CANNOT PERFORM ANY DML OPERATIONS
           ON SAT -SUN AND AFTER 6 PM****');
  END  IF;
  END; 
Row Triggers
¢The trigger body executes once for each row affected by the triggering event.
¢Syntax for creating Row Triggers:
   CREATE  [ OR REPLACE ]  TRIGGER  trigger_name
  trigger_timing  event1  [ OR  event2  OR event3 ]
  ON  table_name
  FOR  EACH  ROW
  [WHEN  condition]
  PL/SQL Block; 
  FOR EACH ROW: Designates the trigger to be a row trigger
   WHEN condition: Specifies the trigger restriction
   For the UPDATE event there is an optional clause
  [OF column_name[,column_name…..]]  
Using OLD and NEW Qualifiers
¢In a ROW LEVEL trigger all the column values of the current row, before modification and after modification, are available to the trigger block as local variables. To access these values, the OLD and NEW quanlifiers are used
  e.g. :OLD.empno, :NEW.deptno

:old and :new variables: Example 
CREATE TABLE emp_hist
( empno NUMBER(5),
 oldSal NUMBER(10),
 newSal NUMBER(10), who varchar2(10), when datetime);
CREATE OR REPLACE TRIGGER  LOG_TRIG
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
 IF :NEW.sal < :OLD.sal THEN
              RAISE_APPLICATION_ERROR(-20101,’Salary cannot be  decremented’);
    ELSE
     INSERT INTO emp_hist VALUES
  (:OLD.empno, :OLD.sal, :NEW.sal, user, sysdate);
   END IF;
END;
few more examples

create or replace trigger tbi before insert on emp for each row
begin
  if :new.sal>5000 then
  :new.sal:=5000;
  end if;
  end;
 /
Trigger created
  create or replace trigger tai after insert on emp for each row
  begin
  if :new.sal>5000 then
  :new.sal:=5000;
  end if;
  end;
 /
ORA-04084: cannot change NEW values for this trigger type
create or replace trigger tbi before insert on emp for each row
 begin
 if :new.sal>5000 then
 :new.sal:=5000;
 :old.sal:=650;
 end if;
 end;
/
ORA-04085: cannot change the value of an OLD reference variable
create or replace trigger tai after insert on emp for each row
 begin
 if :new.sal>5000 then
 :old.sal:=900;
 :new.sal:=5000;
 end if;
 end;
/
Sequence of Execution of Triggers
¢Multiple triggers can be created on the same table for the same event.
¢The order of execution is as follows
1.Execute all BEFORE STATEMENT triggers
2.For each row in the target table:
  (a) Execute all BEFORE ROW triggers
  (b) Execute the DML statement and perform integrity     constraint checking
  (c) Execute all AFTER ROW triggers
3.Execute all AFTER STATEMENT triggers
Sequence of Execution of Triggers : Example
CREATE OR REPLACE TRIGGER t1 BEFORE UPDATE ON emp
 BEGIN
    DBMS_OUTPUT.PUT_LINE('This is statement level before update trigger');
 END;
--
 CREATE OR REPLACE TRIGGER t2 BEFORE UPDATE ON emp FOR EACH ROW
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is row  level before update trigger');
 END;
--
 CREATE OR REPLACE TRIGGER t3 AFTER UPDATE ON emp FOR EACH ROW
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is row  level after update trigger');
 END;
--
CREATE OR REPLACE TRIGGER t4 AFTER UPDATE ON emp
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is statement  level after update trigger');
 END;
--
To check the working of the above triggers, issue the following DML statement:

          UPDATE emp SET sal = sal+450 WHERE deptno=10;

Use of UPDATE OF <column name>
If a triggering statement includes a column list, the trigger is fired only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger is fired when any column of the associated table is updated. A column list is applicable only for UPDATE triggering statement and cannot be specified for INSERT or DELETE triggering statements.
Conditional Predicates
¢You can combine several triggering events in one trigger
¢To identify which event has raised the trigger, use conditional predicates:
¢INSERTING
¢UPDATING
¢DELETING
  They return a TRUE value depending upon the DML statement executed
Conditional Predicates :Example

CREATE TABLE audit_table
(user_name VARACHAR2(10),
table_name VARCHAR2(10),
ins NUMBER(4),
del NUMBER(4),
upd NUMBER(4));
INSERT INTO audit_table VALUES('SCOTT','EMP',0,0,0);
INSERT INTO audit_table VALUES('SYSTEM','EMP',0,0,0);
INSERT INTO audit_table VALUES('HR','EMP',0,0,0);
INSERT INTO audit_table VALUES('SYSTEM','DEPT',0,0,0);
--
CREATE  OR  REPLACE  TRIGGER  audit_emp
AFTER  INSERT  OR  UPDATE  OR  DELETE  ON  emp
FOR EACH ROW
BEGIN
     IF  INSERTING  THEN
           UPDATE  audit_table   SET  ins = ins + 1
           WHERE user_name = USER  AND  table_name= 'EMP';
     ELSIF  DELETING  THEN
           UPDATE  audit_table    SET  del = del + 1
           WHERE user_name = USER  AND  table_name= 'EMP';
     ELSIF UPDATING  THEN
    UPDATE  audit_table   SET  upd = upd + 1
    WHERE user_name = USER  AND  table_name= 'EMP';
END IF;
END;
statement executed
WHEN Clause
¢Valid for row triggers only
¢Trigger body executes for those rows that meet the condition
¢Evaluates for each row
¢OLD and NEW variables can be referenced here
¢For OLD and NEW variables we should not use colon (:) in the WHEN condition 
CREATE OR REPLACE TRIGGER TRIG_WHEN
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (old.sal > new.sal)
BEGIN
    RAISE_APPLICATION_ERROR(-20009, ‘Cannot reduce salary’);
END; 
¢Note: If the expression in the WHEN clause evaluates to TRUE for a row, then the trigger body is fired on behalf of that row. However, if the expression evaluates to FALSE, then the trigger body is not fired for that row.
¢Triggers can be somewhat costly in terms of time-consumption, and hence use the WHEN clause to prevent them from firing when unnecessary.   

¢Restrictions on a Trigger
¢By default, Transaction Control Language commands like COMMIT or ROLLBACK are not allowed within
     a trigger body
eg: 

CREATE TRIGGER trig
AFTER INSERT ON emp
BEGIN
   INSERT INTO emp_log VALUES (SYSDATE,user,'Insert on emp');
   COMMIT;
END;
 
 
 INSERT INTO emp(empno) VALUES (1);
 INSERT INTO emp(empno) VALUES (1)
             *
 ERROR at line 1:
 ORA-04092: cannot COMMIT in a trigger
 ORA-06512: at "SCOTT.TAB1_TRIG", line 3
 ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'


To demonstrate the program first create table emp_log using the following command
CREATE TABLE emp_log(
   myDate DATE, who varchar2(40),
  myOperation VARCHAR2(35)
);

Note : COMMIT, ROLLBACK , SAVEPOINT statements are not allowed within the trigger body.
        Triggers are fired implicitly by the triggering event. Thus by default, a trigger is part of the same transaction as the triggering event. Hence   changes made within triggers should be committed or rolled back as part of the transaction in which they execute. By default a trigger is NOT allowed to execute COMMIT or ROLLBACK statements thereby terminating its triggering transaction (with the exception of autonomous triggers). 
Use of TCL Commands in a Trigger
¢Autonomous transactions:
         Autonomous transactions execute separately from the current transaction.
Unlike regular triggers, autonomous triggers
CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON emp
 DECLARE
      -- declare the trigger as separate transaction from the
       --   triggering event
       PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
       INSERT INTO emp_log VALUES (SYSDATE,user, 'Insert on   emp');
       COMMIT; -- allowed only in autonomous triggers
 END;
  

 SQL> INSERT INTO emp(empno) VALUES (1);
 1 row created.
Managing Triggers
¢Disable / Enable trigger:
  ALTER TRIGGER trigger_name ENABLE/DISABLE;
¢
¢Disable / Enable all triggers for a table:
  ALTER TABLE table_name DISABLE/ENABLE ALL TRIGGERS;
¢
¢Dropping Triggers:
  DROP TRIGGER trigger_name;
¢USER_TRIGGERS data dictionary view:
  To query database trigger details stored in a database dictionary
select trigger_name, trigger_type, triggering_event, when_clause, trigger_body from user_triggers where table_name='EMP' 
Examples 
Suppose you have the following triggers on table EMP
create or replace trigger BIS before insert on emp
begin
dbms_output.put_line('BIS');
end;
/
create or replace trigger AIS after insert on emp
begin
dbms_output.put_line('AIS');
end;
/
create or replace trigger BIR before insert on emp for each row
begin
dbms_output.put_line('BIR');
end;
/
create or replace trigger AIR after insert on emp for each row
begin
dbms_output.put_line('AIR');
end;
/
create or replace trigger BUS before update on emp
begin
dbms_output.put_line('BUS');
end;
/
create or replace trigger AUS after update on emp
begin
dbms_output.put_line('AUS');
end;
/
create or replace trigger BUR before update on emp for each row
begin
dbms_output.put_line('BUR');
end;
/
create or replace trigger AUR after update on emp for each row
begin
dbms_output.put_line('AUR');
end;
/
create or replace trigger BDS before delete on emp
begin
dbms_output.put_line('BDS');
end;
/
create or replace trigger ADS after delete on emp
begin
dbms_output.put_line('ADS');
end;
/
create or replace trigger BDR before delete on emp for each row
begin
dbms_output.put_line('BDR');
end;
/
create or replace trigger ADR after delete on emp for each row
begin
dbms_output.put_line('ADR');
end;
/
create or replace trigger AIRWHEN after insert on emp for each row
when (new.sal>=1000)
begin
dbms_output.put_line('AIRWHEN');
end;
/

Now issue the following command

select trigger_name, trigger_type, triggering_event, when_clause, trigger_body from user_triggers where table_name='EMP'
/

User_objects :

Contains name and status of the trigger and the date and time when the trigger was created.

select object_name, status, created from user_objects where object_type='TRIGGER'


Mutating Table Error
¢This error occurs when we create a row level trigger on a table that attempts to access the same table inside the trigger body
 
¢A Row-level trigger can not read from or write to the table, on which it is fired. However a statement level trigger can perform these actions on the table on which it is written
Mutating Table Error Example
-- TRIGGER for checking that there is only 1 President in EMP
  CREATE OR REPLACE TRIGGER checkJob
  BEFORE INSERT OR UPDATE
  OF job
  ON emp FOR EACH ROW
  WHEN (UPPER(NEW.job)='PRESIDENT')
  DECLARE
    CURSOR mycur IS SELECT empno, job from emp;
  BEGIN
      FOR myvar IN mycur loop
                IF myvar.job ='PRESIDENT' THEN
               RAISE_APPLICATION_ERROR(-20001, 'There can be only one president');
       END IF;
      END LOOP;
   END;


¢To avoid MUTATING table error  you could use a temporary table. In place of a single  AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
¢You may also may make use of Autonomous transactions.
Dealing with Mutating Table Error
--Step 1: Create a package specification to contain a flag value
that will be set by row level trigger and read by statement level
trigger
-- package for declaring a global variable
  CREATE OR REPLACE PACKAGE presidentPack as
     flag NUMBER(1) := 0;
  END presidentPack;
--Step 2: Create an after row level trigger that sets the flag
value to 1 when the new value of job is PRESIDENT
--
 CREATE OR REPLACE TRIGGER trRowLevPresident AFTER INSERT OR
UPDATE OF job ON emp
 FOR EACH ROW
 WHEN (UPPER(NEW.job) = 'PRESIDENT')
      BEGIN
            presidentPack.flag := 1;
      END;
--
--Step 3: Create an after statement level trigger that performs
the check.

CREATE OR REPLACE TRIGGER trstateLevPresident AFTER INSERT OR
UPDATE OF job ON emp
   DECLARE
            presidentCount NUMBER(2);
    BEGIN
       IF presidentPack.flag = 1 THEN
  presidentPack.flag := 0; -- resetting the flag
           SELECT count(empno) INTO presidentCount FROM emp
   WHERE UPPER(job) = 'PRESIDENT';
           IF presidentCount > 1 THEN
             RAISE_APPLICATION_ERROR(-20001, 'Only one
   president is allowed');
           END IF;
       END IF;

    END;

Performance Impact of DML Triggers
¢The DML statements that initiate triggers execute slowly because they execute additional SQL statements, and the user might not know that other actions are occurring.
¢The execution time for a trigger event depends on the complexity of the trigger action and whether it initiates other triggers. The time increases as the number of cascaded triggers increases.
¢Also when a trigger is created, Oracle server has to use a background process to continuously monitor the trigger event


Compound Triggers in Oracle 11g

¢Oracle 11g offers a new twist on triggers, the compound trigger, a trigger that can act both before and after an update, insert or delete has occurred. This makes possible the ability in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.
¢Compound triggers can have up to four sections:
¢the BEFORE section
¢the BEFORE EACH ROW section
¢the AFTER EACH ROW section
¢the AFTER section

¢At least two of the sections must be included (including only one of the four would result in a traditional trigger) and it does not matter which two of the sections are used.
¢For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be 'matched' (BEFORE, BEFORE EACH ROW, for instance).
¢Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. 
¢The general syntax is:
create or replace trigger <trigger name>
for <insert|update|delete> <of column_name>
on <tablename> COMPOUND TRIGGER
<declare section> BEFORE <before section>
BEFORE EACH ROW <before each row section>
AFTER EACH ROW <after each row section>
AFTER <after section>
END;
Compound Triggers Example

    create or replace trigger check_raise_on_avg
    for update of sal on emp
    COMPOUND TRIGGER
      Twelve_Percent  constant number:=0.12;

      -- Declare collection type and variable:
  
      TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
                                    INDEX BY VARCHAR2(80);
     Department_Avg_Salaries     Department_Salaries_t;
     TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
     Avg_Salaries                Sal_t;
     TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
     Department_IDs              Deptno_t;

     BEFORE STATEMENT IS
     BEGIN
       SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
         BULK COLLECT INTO  Avg_Salaries, Department_IDs
         FROM               Emp e
         GROUP BY           e.Deptno;
       FOR j IN 1..Department_IDs.COUNT() LOOP
         Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
       END LOOP;
     END BEFORE STATEMENT;

     AFTER EACH ROW IS
     BEGIN
       IF :NEW.Sal - :Old.Sal >
         Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
       THEN
         Raise_Application_Error(-20000, 'Raise too large');
       END IF;
     END AFTER EACH ROW;
   END Check_Raise_On_Avg;
--
select empno, sal from emp;
--
 update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.13 where deptno = 20;


ALL TRIGGERS EXAMPLE
ORA-04085: cannot change the value of an OLD reference variable
CREATE OR REPLACE TRIGGER T1
BEFORE UPDATE OF SAL
ON EMP
DECLARE
   v_tol_sal NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOTAL SALARY before update');
   SELECT SUM(sal) INTO v_tol_sal  FROM emp;
   DBMS_OUTPUT.PUT_LINE(v_tol_sal );
END;
----
CREATE OR REPLACE TRIGGER T2
AFTER UPDATE OF SAL
ON EMP
DECLARE
   v_tol_sal NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOTAL SALARY AFTER update');
   SELECT SUM(sal) INTO v_tol_sal  FROM emp;
   DBMS_OUTPUT.PUT_LINE(v_tol_sal );
END;


update emp set sal=1.2*sal;
----------------------------------------------------------
CREATE OR REPLACE TRIGGER T1
BEFORE UPDATE OF SAL
ON EMP
DECLARE
   v_tol_sal NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOTAL SALARY before update');
   SELECT SUM(sal) INTO v_tol_sal  FROM emp;
   DBMS_OUTPUT.PUT_LINE(v_tol_sal );
END;
--
CREATE OR REPLACE TRIGGER T2
AFTER UPDATE OF SAL
ON EMP
DECLARE
   v_tol_sal NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOTAL SALARY AFTER update');
   SELECT SUM(sal) INTO v_tol_sal  FROM emp;
   DBMS_OUTPUT.PUT_LINE(v_tol_sal );
END;

--
update emp set sal=1.2*sal;
---
/*
Note : The Trigger will be fired  and will disallow on Saturday or Sunday
or after office hours( 8am to 6pm) any DML operation on the EMP table.
When a database trigger fails, the triggering statement is automatically
rolled back by the Oracle Server.
*/
CREATE  OR  REPLACE  TRIGGER  chk_time
BEFORE  INSERT  OR  UPDATE  OR  DELETE
ON  emp
BEGIN
dbms_output.put_line('hi');
    IF  (TO_CHAR(sysdate,'DY')  IN  ('SAT','SUN'))  
          OR (TO_CHAR(sysdate,'HH24')  NOT
           BETWEEN  '08' AND '18')
THEN
        RAISE_APPLICATION_ERROR(-20400, '*****YOU CANNOT PERFORM ANY DML OPERATIONS
           ON SAT -SUN AND AFTER 6 PM****');
END  IF;
END;
/*


Table : DEPT
DEPTNO varchar2
DEPTNAME varchar2

A trigger to prevent insert or update or delete operations on table dept
*/
create or replace trigger check_deptno
before insert or update or delete on dept
begin

if deleting then
  raise_application_error(-20500,'Delete Not allowed');
elsif Inserting then
  raise_application_error(-20501,'Insert Not allowed');
elsif Updating('deptno') then
  raise_application_error(-20502,'Updating of deptno Not allowed');
else
  raise_application_error(-20503,'Updating of Table Not allowed');
end if;
end;
/*
Special Conditional Predicates INSERTING, UPDATING, DELETING, UPDATING('column')
can be used in a single trigger
for combining several triggering events into one single trigger
*/
--
CREATE  OR  REPLACE  TRIGGER  chk_emp_sal
AFTER  UPDATE  OF  sal
ON emp
FOR  EACH  ROW
BEGIN
IF :NEW.sal < :OLD.sal THEN
RAISE_APPLICATION_ERROR(-20101,'Salary cannot be decremented');
END  IF;
END;
--
update emp set sal=2500;

--

create or replace trigger tbi before insert on emp for each row
begin
  if :new.sal>5000 then
  :new.sal:=5000;
  end if;
  end;


create or replace trigger tai after insert on emp for each row
  begin
  if :new.sal>5000 then
  :new.sal:=5000;
  end if;
  end;
----
create or replace trigger tbi before insert on emp for each row
 begin
 if :new.sal>5000 then
 :new.sal:=5000;
 :old.sal:=650;
 end if;
 end;
-----
create or replace trigger tai after insert on emp for each row
 begin
 if :new.sal>5000 then
 :old.sal:=900;
 :new.sal:=5000;
 end if;
 end;
-----
/*
Use of UPDATE OF <column name>
If a triggering statement includes a column list, the trigger is
fired only when one of the specified columns is updated. If a
triggering statement omits a column list, the trigger is fired
when any column of the associated table is updated. A column list
is applicable only for UPDATE triggering statement and cannot be
specified for INSERT or DELETE triggering statements.
*/
CREATE TABLE emp_hist
( empno NUMBER(5),
 oldSal NUMBER(10),
 newSal NUMBER(10), who varchar2(10),when DATE);

CREATE OR REPLACE TRIGGER  LOG_TRIG
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
 IF :NEW.sal < :OLD.sal THEN
           RAISE_APPLICATION_ERROR(-20101,'Salary cannot be  decremented');
    ELSE
    INSERT INTO emp_hist VALUES
(:OLD.empno, :OLD.sal, :NEW.sal, user, sysdate);
   END IF;
END;
SELECT * FROM EMP_HIST;
--
update emp set sal=5500 WHERE DEPTNO=30;
DROP TRIGGER LOG_TRIG;
DROP TRIGGER CHK_EMP_SAL;

--SEQUENCE OF EXECUTION
CREATE OR REPLACE TRIGGER t1 BEFORE UPDATE ON emp
 BEGIN
    DBMS_OUTPUT.PUT_LINE('This is statement level before update trigger');
 END;

 CREATE OR REPLACE TRIGGER t2 BEFORE UPDATE ON emp FOR EACH ROW
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is row  level before update trigger');
 END;

 CREATE OR REPLACE TRIGGER t3 AFTER UPDATE ON emp FOR EACH ROW
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is row  level after update trigger');
 END;

CREATE OR REPLACE TRIGGER t4 AFTER UPDATE ON emp
 BEGIN
     DBMS_OUTPUT.PUT_LINE('This is statement  level after update trigger');
 END;

 --To check the working of the above triggers, issue the following DML statement

UPDATE emp SET sal = sal+450 WHERE deptno=20;
---
--Conditional Predicates: Example
CREATE TABLE audit_table
(user_name VARCHAR2(10),
table_name VARCHAR2(10),
ins NUMBER(4),
del NUMBER(4),
upd NUMBER(4));
INSERT INTO audit_table VALUES('SCOTT','EMP',0,0,0);
INSERT INTO audit_table VALUES('SYSTEM','EMP',0,0,0);
INSERT INTO audit_table VALUES('HR','EMP',0,0,0);
INSERT INTO audit_table VALUES('SYSTEM','DEPT',0,0,0);
--
CREATE  OR  REPLACE  TRIGGER  audit_emp
AFTER  INSERT  OR  UPDATE  OR  DELETE  ON  emp
FOR EACH ROW
BEGIN
     IF  INSERTING  THEN
           UPDATE  audit_table   SET  ins = ins + 1
           WHERE user_name = USER  AND  table_name= 'EMP';
     ELSIF  DELETING  THEN
           UPDATE  audit_table    SET  del = del + 1
           WHERE user_name = USER  AND  table_name= 'EMP';
  ELSIF UPDATING  THEN
    UPDATE  audit_table   SET  upd = upd + 1
    WHERE user_name = USER  AND  table_name= 'EMP';
END IF;
END;

DELETE FROM EMP WHERE EMPNO=101;
----
--Example of WHEN clause
create or replace trigger t1 after update on emp for each row
when (new.sal>=5000)
declare
a number;
begin
dbms_output.put_line('hello');
end;
---
update emp set sal=3500 where deptno=20;
---
update emp set sal=4200 where deptno=20;

--when example
CREATE OR REPLACE TRIGGER TRIG_WHEN
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (old.sal > new.sal)
BEGIN
    RAISE_APPLICATION_ERROR(-20009, ‘Cannot reduce salary’);
END;
---
/*
Note: If the expression in the WHEN clause evaluates to TRUE for a row,
then the trigger body is fired on behalf of that row. However,
if the expression evaluates to FALSE, then the trigger body
is not fired for that row.
Triggers can be somewhat costly in terms of time-consumption,
and hence use the WHEN clause to prevent them from firing when unnecessary. */
--- same example with If
CREATE OR REPLACE TRIGGER  Sal_Check_TRIG
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
 IF :OLD.sal > :NEW.sal  THEN
           RAISE_APPLICATION_ERROR(-20101,'Salary cannot be  decremented');
END IF;
END;



--For Old and NEW variables we should not use : in the WHEN condition

/*By default, Transaction Control Language commands like COMMIT or ROLLBACK are not allowed within a trigger body
e. g.
*/
To demonstrate the program in this and the next slide first create table emp_log using the following command
CREATE TABLE emp_log(
  myDate DATE, who varchar2(40),
myOperation VARCHAR2(35)
);
--To demonstrate the program for tcl commands
--first create table emp_log using the following command
CREATE TABLE emp_log(
  myDate DATE, who varchar2(40),
myOperation VARCHAR2(35)
);

/*
Note : COMMIT, ROLLBACK , SAVEPOINT statements are not allowed
within the trigger body.
Triggers are fired implicitly by the triggering event.
Thus by default, a trigger is part of the same transaction as
the triggering event. Hence   changes made within triggers should be committed
or rolled back as part of the transaction in which they execute.
By default a trigger is NOT allowed to execute COMMIT or ROLLBACK statements
thereby terminating its triggering transaction (with the exception of
autonomous triggers).
*/
CREATE or replace TRIGGER trig
AFTER INSERT ON emp
BEGIN
   INSERT INTO emp_log VALUES (SYSDATE,user, 'Insert on emp');
   COMMIT;
END;


 INSERT INTO emp(empno) VALUES (1);

 --AUTONOMOUS_TRANSACTION;
 BEGIN
       INSERT INTO emp_log VALUES (SYSDATE,user, 'Insert on emp');
       COMMIT; -- allowed only in autonomous triggers
 END;
 

INSERT INTO emp(empno) VALUES (1);

--Conisder the table DEPT as
Create table newdept(deptno number, deptname varchar2(10));

--Consider the table AUDT_DEPT
create table audt_dept(
 User_name varchar2(15),
 Time timestamp,
 Old_deptno varchar2(15),
 New_deptno varchar2(15),
 Old_deptname varchar2(15),
 New_deptname varchar2(15),
Action varchar2(10)
)

--Write a trigger which will record in the table AUDT_DEPT
--as to which user and at what time fired which DML(INSERT, UPDATE or DELETE)
-- on the table DEPT, and also record what were the old and new values of record which was inserted, update or deleted from the EMP table.

create or replace trigger audt_dept
after insert or update or delete on newdept for each row
begin
if inserting then
insert into audt_dept values(user,  sysdate, :old.deptno, :new.deptno, :old.deptname, :new.deptname,’inserting’);
elsif deleting then
insert into audt_dept values(user,  sysdate, :old.deptno, :new.deptno, :old.deptname, :new.deptname,’deleting’);
else
insert into audt_dept values(user,  sysdate, :old.deptno, :new.deptno, :old.deptname, :new.deptname,’updating’);
end if;
end;
--Autonomous transactions:Autonomous transactions are often used
--for logging errors in Oracle PL/SQL applications.
--By making the error logging procedure autonomous
/*Autonomous transactions execute separately from the current transaction.
Unlike regular triggers, autonomous triggers can/must contain COMMIT
and ROLLBACK statements.
*/
CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON emp
 DECLARE
   -- declare the trigger as separate transaction from the
       --   triggering event
       PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
       INSERT INTO emp_log VALUES (SYSDATE,user, 'Insert on emp');
       COMMIT; -- allowed only in autonomous triggers
 END;
 
--
INSERT INTO emp(empno) VALUES (1);
--Managing Triggers

-----Disable / Enable trigger:
ALTER TRIGGER T1 DISABLE;
  ALTER TRIGGER T1 ENABLE;

---Disable / Enable all triggers for a table:
ALTER TABLE EMP DISABLE ALL TRIGGERS;
  ALTER TABLE EMP ENABLE ALL TRIGGERS;

--Dropping Triggers:
DROP TRIGGER T1;

--managing triggers
--USER_TRIGGERS data dictionary view:
--To query database trigger details stored in a database dictionary
select trigger_name, trigger_type, triggering_event, when_clause, trigger_body from user_triggers where table_name='EMP'


--User_objects :
/*Contains name and status of the trigger and the date and time when the trigger was created.*/

select object_name, status, created from user_objects where object_type='TRIGGER';

--Mutating Table Error
--TRIGGER for checking that there is only 1 President in EMP
  CREATE OR REPLACE TRIGGER checkJob
  BEFORE INSERT OR UPDATE
  OF job
  ON emp FOR EACH ROW
  WHEN (UPPER(NEW.job)='PRESIDENT')
  DECLARE
  CURSOR mycur IS SELECT empno, job from emp;
  BEGIN
    FOR myvar IN mycur loop
              IF myvar.job ='PRESIDENT' THEN
              RAISE_APPLICATION_ERROR(-20001, 'There can be only one president');
      END IF;
    END LOOP;
   END;
   UPDATE EMP SET JOB ='PRESIDENT' WHERE EMPNO=7900;
/*
To avoid MUTATING table error  you could use a temporary table.
In place of a single  AFTER row trigger that updates the original table,
resulting in a mutating table error, you may be able to use two triggers
—an AFTER row trigger that updates a temporary table,
and an AFTER statement trigger that updates the original table
with the values from the temporary table.
You may also may make use of Autonomous transactions.
*/
--Step 1: Create a package specification to contain a flag value that will be set by row level trigger and read by statement level trigger

-- package for declaring a global variable

CREATE OR REPLACE PACKAGE presidentPack as
     flag NUMBER(1) := 0;
END presidentPack;
--Step 2: Create an after row level trigger that sets the flag value to 1 when the new value of job is PRESIDENT
CREATE OR REPLACE TRIGGER trRowLevPresident AFTER INSERT OR UPDATE OF job ON emp
 FOR EACH ROW
 WHEN (UPPER(NEW.job) = 'PRESIDENT')
      BEGIN
            presidentPack.flag := 1;
      END;

--Step 3: Create an after statement level trigger that performs the check.

CREATE OR REPLACE TRIGGER trstateLevPresident AFTER INSERT OR UPDATE OF job ON emp
   DECLARE
            presidentCount NUMBER(2);
    BEGIN
       IF presidentPack.flag = 1 THEN
presidentPack.flag := 0; -- resetting the flag
           SELECT count(empno) INTO presidentCount FROM emp
WHERE UPPER(job) = 'PRESIDENT';
           IF presidentCount > 1 THEN
            RAISE_APPLICATION_ERROR(-20001, '**************Only one
president is allowed**********');
           END IF;
       END IF;
    END;
 
    --
    DROP TRIGGER CHECKJOB;
    UPDATE EMP SET JOB ='PRESIDENT' WHERE EMPNO=7900;
   ALTER TABLE EMP DISABLE ALL TRIGGERS;
    --COMPOUND TRIGGERS
    create or replace trigger check_raise_on_avg
    for update of sal on emp
    COMPOUND TRIGGER
      Twelve_Percent  constant number:=0.12;

      -- Declare collection type and variable:

      TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
                                    INDEX BY VARCHAR2(80);
     Department_Avg_Salaries     Department_Salaries_t;
     TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
     Avg_Salaries                Sal_t;
     TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
     Department_IDs              Deptno_t;

     BEFORE STATEMENT IS
     BEGIN
       SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
         BULK COLLECT INTO  Avg_Salaries, Department_IDs
         FROM               Emp e
         GROUP BY           e.Deptno;
       FOR j IN 1..Department_IDs.COUNT() LOOP
         Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
       END LOOP;
     END BEFORE STATEMENT;

     AFTER EACH ROW IS
     BEGIN
       IF :NEW.Sal - :Old.Sal >
         Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
       THEN
         Raise_Application_Error(-20000, 'Raise too large');
       END IF;
     END AFTER EACH ROW;
   END Check_Raise_On_Avg;
--
select empno, sal from emp;
--
 update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.13 where deptno = 20;

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)