Written By:- Isha Malhotra
Our Website:-Tech Altum
Our Website:-Tech Altum
Trigger
Triggers can be defined as an area of code
Which executes in response of a particular action like insert, update, delete,
Create, drop etc. Triggers executes automatically.
Type of triggers
1. DML (Data Manipulation
Language) Trigger
A DML trigger is
Fired in response of DML statement like insert, update, delete etc.
Syntax:-
Create trigger trg_name on Table_name
For/after/instead of Action (insert, update, delete)
As
Your statement…………………………
Type of DML Trigger:-
After /For Trigger
This trigger runs after insert, update and
Delete command.
For
example:-
Suppose I want to create tracking system
for my table. As I used in my project that if any user delete any record from
table then this record insert into the another table too, So that in future we
can track that how many rows deleted in this table. In that case I used after
delete trigger as shown following:-
create
Trigger trg_afterDel on
emp
after
delete
as
declare
@id int;
declare
@sal int;
select
@id=deleted.id from deleted;
select
@sal=deleted.salary
from deleted;
insert into emp_track values(@id, @sal);
after executing this
trigger whenever you delete any record from emp, it will be deleted from emp
and inserted into emp_track.
Similary you can use
insert and update trigger.(for this you will select data from inserted)
Insetead of Trigger
Sometimes when we want to verify
our data in command, in that case we use instead of trigger. Instead of
performing that action(insert, update, delete) the server execute this trigger.
For Example
create
trigger trg_insUpd on
emp
instead
of update
as
declare
@id int;
declare
@sal int;
select
@id=inserted.id
from inserted;
select
@sal=inserted.salary
from inserted;
if( @sal=0)
begin
rollback
end
else
begin
update
emp set salary=@sal
where id=@id
commit
end
for example if anybody try to
update salary with 0 then it will not update that data as I dnt want to put 0
in salary. After executing this trigger if I try to update record with salary 0
it will show error.
2.
DDL (Data Definition
Language) Trigger
A DDL trigger is fired in response of DDL
statement like drop, create, alter etc.
Syntax for DDL trigger
Create trigger trg_name on Database
For/after/instead of Action(create, drop, alter)
As
Your statements………………………..
For example:-
If you prevent your database to drop any
table then you will write trigger as follows:-
create
trigger trg_creat on
DATABASE
for
DROP_TABLE
as
print
'YOU CAN NOT DROP ANY TABLE';
rollback;
Now if you want to drop the table the
server will fire this trigger.
Join our Facebook
page to get updates of Jobs, Interview Question, Course Details and Study
Material
Nice Article ..
ReplyDelete