Trigger

Trigger

A Trigger:

- Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema or the database.

- Executes implicitly whenever a particular event takes place.
- Can be either:
DML Trigger - fires whenever INSERT, UPDATE, DELETE
Schema Trigger – CREATE, ALTER, DROP
DatabaseTrigger - fires whenever system events (such as
Logon, shutdown or startup) occurs on a schema or a database

Creating DML Triggers

A triggering statement contains:

- Trigger Timing
- for table: BEFORE, AFTER
- for View: INSERT, UPDATE or DELETE
- Triggering Event: On table, View
- Trigger Type: Row or Statement
- WHEN Clause: Restricting condition
- Trigger body: PL/SQL block

DML Trigger Components

BEFORE: Execute the trigger body before the triggering DML event on a table.
AFTER: Execute the trigger body after the triggering DML event on a table.
INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views
that are not otherwise modifiable.

Triggering user events: The triggering event is an Insert, Update & Delete statement on a table.

Trigger type
Statements: The trigger body executes once for the triggering events. This is the default. A
statement trigger fires once, even if no rows are affected at all.

Row: The trigger body executes once for each row affected by the triggering events. A row trigger is not executed if the triggering events affect no rows.



Firing Sequence
----------------
----> BEFORE statement trigger
Empno Ename Deptno
----- ------ ------ -----> BEFORE row trigger
7902 SMITH 20
7876 JONES 10 -----> AFTER row trigger
7878 SURESH 30

----> AFTER statement trigger

Syntax for creating DML Statement Triggers

CREATE [OR REPLACE] trigger Trigger_name
timing
events1 [OR events2 OR events3 ]
ON table_name
Trigger_body
------------------------------------------------------------------------
create or replace trigger emp_Bef_inst
before insert on emp
begin
dbms_output.put_line('before') ;
end ;
------------------------------------------------------------------------

create or replace trigger emp_aft_inst
after insert on emp
begin
dbms_output.put_line('after') ;
end ;
---------------------------------------------

create or replace trigger res_salary
before insert
on emp
begin
dbms_output.put_line('welcome');
end;

------------------------------------------

create or replace trigger aft_ins
after insert on emp for each row
declare
meno emp.empno%type ;
begin
meno := :new.empno ;
dbms_output.put_line('New employee no is ' ||meno) ;
end ;
--------------------------------------------------

create or replace trigger res_salary
before insert
on emp1
begin
RAISE_APPLICATION_ERROR (-20202,'Employee cannot insert');
end;

insert into emp1 (empno,ename,sal,deptno) values (3333,'rrr',3333,40) ;
------------------------------------------------------------------------
create or replace trigger res_salary
before update of sal
on emp1
begin
RAISE_APPLICATION_ERROR (-20202,'Employee cannot update');
end;

update emp1 set sal=3333 where empno=7900 ;

------------------------------------------------------------------------
create or replace trigger res_salary
before delete
on emp1
begin
RAISE_APPLICATION_ERROR (-20202,'Employee cannot delete');
end;

delete from emp1 where empno=7900 ;
------------------------------------------------------------------------
CREATE TRIGGER SalaryCheck
AFTER INSERT OR UPDATE OF sal ON emp1
FOR EACH ROW
BEGIN
IF (:new.sal <>
RAISE_APPLICATION_ERROR(-20000, 'no negative salary allowed');
END IF;
END;
------------------------------------------------------------------------
create or replace trigger lunch
before insert or update or delete on emp
declare
a varchar2(4);
begin
select to_char(sysdate,'HHMM') into a from dual ;
if a between '0100' and '0200' then
raise_application_error(-20202,'U canot do ant DML operation during lunch time') ;
end if ;
end ;
----------------------------------------------------------------------------------------------
create or replace trigger secure_emp
before insert on emp1
begin
if (to_char(sysdate,'DY') in ('SAT','SUN')) or
(to_char(sysdate,'HH24:MI') not between '08:00' and '18:00')
then raise_application_error (-20500,'You may insert into emp table only during businees');
end if ;
end;

Trigger using conditional Predicates

create or replace trigger secure_emp
before insert or update or delete on emp1
begin
if (to_char(sysdate,'DY') in ('SAT','SUN'))
or (to_char(sysdate,'HH24:MI') not between '08:00'and '18:00')
then
if deleting then
raise_application_error (-20500,'You may deleting emp table only during businees');
elsif inserting then
raise_application_error (-20500,'You may insert into emp table only during businees');
elsif updating ('SAL') then
raise_application_error (-20500,'You may updating emp table only during businees');
else
raise_application_error (-20500,'You may update into emp table only during businees');
end if;
end if ;
end;

Restricting a ROW triggers

create or replace trigger derive_comm_pct
after insert or update or delete
on emp1
for each row
WHEN (new.job='MANAGER')
begin
if inserting then
:new.comm := 0 ;
elsif :old.comm is null then
:new.comm := 0 ;
else
:new.comm := :old.comm + 0.05 ;
end if;
end;

Trigger (ROW LEVEL)(Using :old qualifier)

create or replace trigger restrict_salary
before insert or update of sal on emp1
for each row
begin
if not (:new.job in ('MANAGER','CLERK')) and :new.sal > 15400
then
raise_application_error(-20202,'Employee cannot earn this amount') ;
end if ;
end;

Trigger (ROW LEVEL)(Using :old and :new qulaifier)

create table audit_emp_table
(USERNAME VARCHAR2(30),
TIMESTAMP DATE,
ID VARCHAR2(20),
OLD_LAST_NAME VARCHAR2(30),
NEW_LAST_NAME VARCHAR2(30),
OLD_TITLE VARCHAR2(20),
NEW_TITLE VARCHAR2(20),
OLD_SALARY NUMBER(9,2)
NEW_SALARY NUMBER(9,2));
-------------------------------------------
create or replace trigger audit_emp_values
after insert or update or delete on emp1
for each row
begin
insert into AUDIT_EMP_TABLE (USERNAME,TIMESTAMP,ID,OLD_LAST_NAME,
NEW_LAST_NAME,OLD_TITLE,NEW_TITLE,OLD_SALARY,NEW_SALARY)
values (user, sysdate, :old.empno, :old.ename, :new.ename, :old.job,
:new.job, :old.sal, :new.sal);
end;

select * from audit_emp_table ;

insert into emp1 (empno,ename,job,sal,deptno)
values (3456,'dddd','MANAGER',2456,30) ;

Trigger (ROW LEVEL) (Using as Instead of Trigger)

INSTEAD OF Triggers
Use INSTEAD OF Triggers to modify data in which the DML statement has been issued against an inherently non-updateable view. These triggers are called INSTEAD OF triggers.

Syntax for Creating an INSTEAD of Trigger
-----------------------------------------

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
events [or event2 or event3]
ON view_name
[Referencing

----------------------------------------------

create table new_emp as select empno,ename,sal,deptno,hiredate,job from emp ;

create table new_depts as select e.deptno,e.dname, sum(d.sal) tot_dept_sal
from emp d, dept e where d.deptno=e.deptno group by e.deptno,e.dname ;

create view emp_details as select e.empno,e.ename,e.sal,e.deptno,e.job,d.dname,d.loc
from emp e, dept d where d.deptno=e.deptno ;



create or replace trigger new_emp_det
instead of insert or update or delete on emp_details
for each row
begin
if inserting then
insert into new_emps values ( :new.empno,:new.ename,:new.sal,:new.deptno,sysdate,:new.job) ;

update new_depts set tot_dept_sal = tot_dept_sal + :new.sal where deptno = :new.deptno ;
elsif deleting then
delete from new_emps where empno= :old.empno;
update new_depts set tot_dept_sal = tot_dept_sal - :old.sal where deptno = :new.deptno ;
elsif updating ('sal') then
update new_emps set sal = :new.sal where empno = :new.empno ;
update new_depts set tot_dept_sal = tot_dept_sal + :new.sal - :old.sal
where deptno = :new.deptno ;
elsif updating ('deptno') then
update new_emps set deptno = :new.deptno where empno = :old.empno ;
update new_depts set tot_dept_sal = tot_dept_sal - :old.sal where deptno = :old.deptno ;
update new_depts set tot_dept_sal = tot_dept_sal + :new.sal where deptno = :new.deptno ;
end if ;
end;


insert into EMP_DETAILS values (1000,'suresh',3456,30,'MANAGER','ACCOUNTING') ;


2) create table NEW_EMP as select empno,ename,deptno from emp ;

create table new_dept as select deptno,dname from dept ;

create view emp_dept_view as select e.empno,e.ename,e.deptno,d.dname from emp e ,dept d where e.deptno=d.deptno ;

create or replace trigger ins_trigs
instead of insert on emp_dept_view
begin
insert into new_emp values (:new.empno,:new.ename,:new.Deptno) ;
insert into new_dept values (:new.deptno,:new.dname) ;
end ;

insert into emp_dept_view values (2345,'dddd',20,'ffff') ;
--------------------------------------------------------------------------------------------

MANAGING TRIGGERS

Alter trigger trigger_name Disable | enable ;
Alter table table_name Disable|ename ALL triggers ;
Drop trigger trigger_name ;

Creating Database Triggers

Triggering user Events:

- CREATE, ALTER or DROP

- Logging on or off

Triggering database or system event:

- Shutting down or starting up the database

- A specific error (or any error) being raised.


Creating Trigger on DDL Statements

These Trigger will be fired implicitely whenever user performs any DDL operations like create, alter or dropping an object
The Syntax is :
Create or replace trigger
Before|after DDL statement on schema
PL/SQL Block

Example
Before create
Create or replace trigger ddl_trigger
Before create on schema
begin
Raise_application_error(-20222,’U cannot create a table’)
end;

After create
Create or replace trigger ddl_trigger
after create on schema
begin
insert into audit_objects_use values (user||’ has created an object at’||to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) ;
end;
LOGON and LOGOFF Trigger Example

create table LOG_TRIG_TABLE
(
USER_ID varchar2(10),
LOG_DATE date,
ACTION varchar2(20)) ;

create or replace trigger logon_trigs
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO LOG_TRIG_TABLE (USER_ID,LOG_DATE, ACTION)
VALUES (user,sysdate,'Logging On') ;
end;

create or replace trigger logoff_trigs
before LOGOFF ON SCHEMA
BEGIN
INSERT INTO LOG_TRIG_TABLE (USER_ID,LOG_DATE, ACTION)
VALUES (user,sysdate,'Logging Off') ;
end;

select * from LOG_TRIG_TABLE ;


Viewing Trigger Information

You can view the following trigger inormation by using USER_TRIGGERS

SQL> select trigger_name, trigger_type, triggering_event, table_name,
Referencing_names, status, trigger_body from USER_TRIGGERS
Where trigger_name='CHECK_SALARY’;