TRIGGER

TRIGGER

*****used table for creating trigger**********
create table trig_test(order1 char);

------------------------------------------------------
a simple "DML STATEMENT TRIGGER"trigger

create or replace trigger trig1 before insert or delete or update on stud
begin
if inserting then insert into trig_test values('I');
elsif deleting then insert into trig_test values('d');
elsif updating then insert into trig_test values('U');
end if;
end;
/
-------------------------------------------------------
checking the trigger
insert into stud(roll) values(20);
insert into stud(roll) values(30);
select * from trig_test;
update stud set roll=10 where roll=20;
delete from stud where roll=10;
select * from trig_test;

----------------------------------------------------------
used table and values
insert into stud values('arka',1,'16-may-1985');
insert into stud values('dibyendu',2,'29-dec-1982');
insert into stud values('gora',3,'21-may-1989');
create table oldstud(name varchar2(20),roll number(5),dob date);
---------------------------------------------------------
A SIMPLE "DML ROW LEVEL TRIGGER"
create or replace trigger trig2 after delete or insert or update on stud for each row
begin
if inserting then
dbms_output.put_line('one row inserted');
elsif deleting then
insert into oldstud values(:old.name,:old.roll,:old.dob);
dbms_output.put_line('one row deleted');
elsif updating then
insert into oldstud values(:old.name,:old.roll,:old.dob);
dbms_output.put_line('one row updated');
end if;
end;
/

insert into stud values('india',6,'15-aug-1947');
update stud set roll=10 where name='arka';
---------------------------------------------------------------

Managing trigger

-----------disable or enable trigger-----
alter trigger trig1 disable;
alter trigger trig1 enable;

-----------disable or enable all trigger on a table ---------
alter table stud disable all triggers;
alter table stud enable all triggers;

----recompile trigger-----------------------
alter trigger trig1 compile;

-------drop trigger-----------
drop trigger trig1;
------------------------------------------------------

LOGON LOGOF TRIGGER

used table
create table LOGIN_CHECKER(a varchar2(20));

CREATE OR REPLACE TRIGGER logged
AFTER LOGON ON SCHEMA
begin
INSERT INTO LOGIN_CHECKER VALUES('T');
end;

CREATE OR REPLACE TRIGGER log_out
AFTER LOGON ON SCHEMA
begin
INSERT INTO LOGIN_CHECKER VALUES('F');
end;

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

CALL statement

used procedure

CREATE OR REPLACE PROCEDURE PROC_TRIG IS
BEGIN
INSERT INTO LOGIN_CHECKER VALUES('INSERTED');
END;

TRIGGER BODY

CREATE OR REPLACE TRIGGER CALL_TRIG
AFTER INSERT ON STUD FOR EACH ROW
CALL PROC_TRIG
/

INSERTING VALUES
INSERT INTO STUD(ROLL) VALUES(78);

CHECKING
SELECT * FROM LOGIN_CHECKER;

--------------------------------------------------
controlling sesurity through trigger

CREATE OR REPLACE trigger SECU_STUD
BEFORE INSERT ON STUD FOR EACH ROW
BEGIN IF(TO_CHAR(SYSDATE,'DY') IN ('SAT','FRI')) THEN
RAISE_APPLICATION_ERROR(-20503,'Please Do not change the value it is problemetic for Arka');
end if;
end;

now check
insert into stud(roll) values(145);
--------------------------------------------------