TRIGGER

automatically fires whenever an event occurs in a table, schema or a database.

Types

Statement Level / Table level trigger

	it fires once for each event. This is the default trigger. 	it fires once even if no rows are affected by the triggering event.

Row Level trigger
	
	it fires once for each ROW affected by triggering event.  this trigger will not fire when no rows are affected by the triggering event.


Trigger Timings

before
after
instead of

Triggering Event

insert
update
delete
logon 
logoff

drop table audit_log;

create table audit_log
(
usrnme		varchar(30),
logon_time	timestamp,
logoff_time	timestamp
);

CREATE OR REPLACE TRIGGER TRG_LOGON
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO audit_log VALUES (user,sysdate,null);
END;
/

CREATE OR REPLACE TRIGGER TRG_LOGOFF
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO audit_log VALUES (user,null,sysdate);
END;
/

CREATE OR REPLACE TRIGGER TRG_T1
BEFORE INSERT OR UPDATE OR DELETE ON T1
BEGIN

IF TO_CHAR(SYSDATE,'D') IN (1,7) OR TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '10' AND '18'
THEN
RAISE_APPLICATION_ERROR(-20000,'INSERT NOT ALLOWED ON WEEKENDS');
END IF;

END;
/

Mutating table error
================

create or replace trigger trg_t1
before insert on t1
begin
insert into t1 values (5);
end;
/

CREATE OR REPLACE TRIGGER TRG_T1_BACKUP
BEFORE INSERT OR UPDATE OR DELETE ON T1
FOR EACH ROW
BEGIN

if INSERTING then
insert into t2 values (:NEW.A);

elsif UPDATING then

UPDATE T2 SET B = :NEW.A WHERE B = :OLD.A;

elsif DELETING then

delete from t2 where B = :OLD.A;

end if;

END;
/

Compound Triggers		Oracle 11g New Feature

A compound trigger allows code for one or more timing points 
for a specific object to be combined into a single trigger.

CREATE OR REPLACE TRIGGER trg_t1
  FOR insert ON T1
    COMPOUND TRIGGER

  BEFORE STATEMENT IS
  BEGIN
    insert into t2 values (1);
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    insert into t2 values (2);
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    insert into t2 values (3);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    insert into t2 values (4);
  END AFTER STATEMENT;

END TRG_T1;
/

create or replace trigger trg_t1_1
before insert on t1
begin
insert into t2 values (1);
end;
/

create or replace trigger trg_t1_2
after insert on t1
begin
insert into t2 values (2);
end;
/

create or replace trigger trg_t1_3
before insert on t1
for each row
begin
insert into t2 values (3);
end;
/

create or replace trigger trg_t1_4
after insert on t1
for each row
begin
insert into t2 values (4);
end;
/

T1

Statement level
BEFORE
	INSERT
	UPDATE
	DELETE
AFTER
	INSERT
	UPDATE
	DELETE

Row level
BEFORE
	INSERT
	UPDATE
	DELETE
AFTER
	INSERT
	UPDATE
	DELETE