Database Triggers

Trigger :
The PLSQL block connected to the database, view, table, or schema. When a specific event occurs, it can be explicitly executed in either

Application trigger :
Fires in response to any event that takes place with a specific application.

Database Trigger :
Fires whenever a schema/database experiences a data event (DML) or a system event (logon/shutdown).One cannot invoke or execute a trigger. When there is a change in data in the related table, the DBMS initiates the trigger automatically. By methodically altering the data, triggers are utilized to preserve the referential integrity of the data.

Advantages of Trigger :

Produce values automatically.

Documenting the modifications in order to audit the data in a table.

Automatically alerting other applications when they need to be changed.

Gathering and preserving statistical data.

Upholding corporate regulations and referential integrity.

Event recording and data storage for table access.

Automatically producing some values for derived columns.

Triggers can also carry out stored procedures.

Imposing security authorizations

Imposing authorizations for security.

Stopping erroneous transactions

Table replication in synchrony.

Trigger Based on Timing :
Before
After
Instead of

Timing Points :
DML triggers have four basic timing points for a single table
Before statement
Before Each Row
After Each Row
After Statement

Trigger Types :


    Statement level triggers	       Row level triggers	            Instead of trigger
  ----------------------------     ----------------------------      ---------------------------------
      before insert	              before insert on each row	    
      before update	              before update on each row	        mainly using for complex views.
      before delete	              before delete on each row	
      After insert	              after insert on each row	
      After update	              after update on each row	
      After delete	              after delete on each row

Trigger Events :
Database Events :

  AFTER STARTUP
  BEFORE SHUTDOWN
  AFTER DB_ROLE_CHANGE
  AFTER SERVERERROR
  AFTER LOGON
  BEFORE LOGOFF
  AFTER SUSPEND

DDL Events :

  ALTER
  ANALYZE
  ASSOCIATE STATISTICS
  AUDIT
  COMMENT
  CREATE
  DISASSOCIATE STATISTICS
  DROP
  GRANT
  NOAUDIT
  RENAME
  REVOKE
  TRUNCATE
  DDL

Statement Level Trigger :
Triggers at the statement level for transactions. It runs once every transaction. Usually, they are employed to impose extra security on table transactions. When the FOR EACH ROW clause is removed, the default type of triggers created is recognized.
Syntax :

  create trigger < trigger_name >
  trigger timming
  trigger event 
  on
  object name 
  begin
  triggering body
  end;

Statement level trigger :

  SQL> create or replace trigger tesdb_trig1
  before insert or update or delete
  on emp
  begin
  if (to_char(sysdate,'DY') in (‘SAT','SUN'))
  then
  raise_application_error
  (-20998,‘You are allowed only official days');
  end if;
  end;
  /


  Trigger created.
  ORA-20998: You are allowed only official days
  ORA-06512: at "SCOTT.TESDB_TRIG1", line 4
  ORA-04088: error during execution of trigg er 'SCOTT.TESDB_TRIG1'

Row Level trigger :
Triggers at the row level for data-related task. Every row in a transaction has a single trigger execution. The most prevalent kind of triggers, which are frequently employed in applications for data auditing. The CREATE TRIGGER command's FOR EACH ROW clause identifies the trigger.
Syntax :

  create trigger < trigger_name >
  trigger timming
  trigger event 
  on
  object name 
  for each row;
  begin
  triggering body
  end;

Row Level Trigger :

  create or replace trigger tesdb_trig2
  before insert or update of sal on emp
  for each row
  begin
  if not(:new.sal>10000)
  then
  raise_application_error
  (-20025,'salary must be more then ten thousand');
  end if;
  end;
  /
  Trigger created
  SQL> update emp set sal=1000 where ename='SCOTT';
  update emp set sal=1000 where ename='SCOTT‘
  *
  ERROR at line 1:
  ORA-20025: salary must be more then ten thousand
  ORA-06512: at "SCOTT.TESDB_TRIG2", line 4
  ORA-04088: error during execution of trigger 'SCOTT.TESDB_TRIG2'

Row Level Trigger :

  A Triggers automatically turns to capital letters before store into table

  SQL> create or replace trigger tesdb_upper_trig3
  before insert or update
  on dept
  for each row
  begin
  :new.dname := upper(:new.dname);
  :new.loc := upper(:new.loc);
  end;
  /
  Trigger created.
  SQL> insert into dept values(50,'marketing','chennai');
  1 row created
  SQL> select * from dept;
  DEPTNO       DNAME               LOC
  10                  ACCOUNTING    NEW YORK
  20                  RESEARCH          DALLAS
  30                  SALES                   CHICAGO
  40                  OPERATIONS       BOSTON
  50                  MARKETING        CHENNAI

Enable/Disable a Trigger :
To disable the trigger :

  SQL> alter trigger tesdb_trig1 disable;
  Trigger altered.

To enable the trigger :

  SQL> alter trigger tesdb_trig1 enable;
  Trigger altered.

To disable all trigger into the table :

  SQL> alter table emp disable all triggers;
  Table altered.

To enable all trigger into the table :

  SQL> alter table emp enable all triggers;
  Table altered.

To drop a trigger :

  SQL> drop trigger tesdb_trig4;
  Trigger dropped.

View for Triggers :

  dba_triggers
  all_triggers
  user_triggers
  SQL> select trigger_name, table_name, trigger_type, status from user_triggers;
  TRIGGER_NAME            TABLE_NAME           TRIGGER_TYPE                      STATUS
  TESDB_TRIG1              EMP               BEFORE  STATEMENT                 DISABLED
  TESDB_TRIG2              EMP               BEFORE   EACH ROW                 DISABLED
  TESDB_UPPER_TRIG3        DEPT              BEFORE   EACH ROW                 ENABLED

Restriction to Trigger :
No transaction control statement (such as a commit or rollback) may be issued by a trigger. No LONG variables can be declared by the trigger body. Constraining or modifying tables might not be accessible to row-level triggers.

A mutating table is one that the current trigger continuously scans for changes to. A constraining table is one that uses a foreign key to link to a mutating table.

« Previous Next Topic » (PL/SQL - Procedures and Functions)