Trigger

Trigger : 

Triggers are fired when the following events occur.

    The following events are :

    1. insert,update,delete -- DML 

    2. create,alter,drop -- DDL

    3. Login,Logoff,shutdown of the database.

Why Triggers?

 Suppose when some one is inserting in the table where you are the owner. then you can know that some one is inserting the data in the table that you own. We can say its for handling the events and for Security.

The basic syntax of the trigger is:

CREATE [OR REPLACE] TRIGGER <TRIGGERNAME>

[BEFORE | AFTER ][INSERT|UPDATE|DELETE] ON <TABLENAME>

BEGIN

 <STATEMENTS LIKE DBMS OUTPUT>

END;

/

Note : here triggers are created once and executed when ever a event is fired on the table specified in the creation of the trigger.

we cannot call trigger in the procedure or function , since it is a event handler , it is dynamic, that is it is fired when the specific queries are fired. 

Conclusion :

 Triggers are stored in the database once it is compiled and fired whenever the event occurs.

Below is the example:

 1. Create a table:

    create table student(sid number primary key,firstname varchar2(50) not null,lastname varchar2(50) not null,email varchar2(50) not null unique);

2. set the server output on:

 set serveroutput on;

3. create trigger for insert

  create or replace trigger studentinserttrigger

before insert on student

for each row

begin

 dbms_output.put_line('you are inserting into the student table');

end;

/

when the trigger is compiled you will get the following log:

 TRIGGER STUDENTINSERTTRIGGER compiled.

4. now lets insert into the table:

 insert into student values(1,'raaj','saraff','raajsaraff@self.com')

5. the log on console will be:

   1 rows inserted

    you are inserting into the student table

here in the above example , trigger is fired when the record in inserted.

In the same way, triggers for update,delete are created.

Now lets write a update trigger for the student table.

 create or replace trigger studentupdatetrigger

after update on student

for each row

begin

 dbms_output.put_line('you are updating the student table');

end;

/

the log will be: 

TRIGGER STUDENTUPDATETRIGGER compiled

Now update the record that is inserted.

update student set email = 'raajsaraff@saraff.in' where sid = 1

the console log  will be :

1 rows updated

you are updating the student table .

Now lets create trigger on delete:

create or replace trigger studentdeletetrigger 
before delete on student
begin
 dbms_output.put_line('you are deleting from student');
end;
/

The log will be :
 TRIGGER STUDENTDELETETRIGGER compiled

now just delete the record :
 delete from student;
The log will be :
1 rows deleted
 you are deleting from student.


Dropping a trigger:

drop trigger studentinserttrigger

log will be:
drop trigger studentinserttrigger succeeded.