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
DML (Data Manipulation Language) Trigger
DDL (Data Definition Language) Trigger
A DML trigger is fired in response of DML statement like insert, update, delete etc.
Create trigger trg_name on Table_name
For/after/instead of Action (insert, update, delete)
As
Your statement…………………………
There are two types of DML Trigger
This trigger runs after insert, update and Delete command.
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. Similarly you can use insert and update trigger.(for this you will select data from inserted)
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.
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 don’t want to put 0 in salary. After executing this trigger, if I try to update record with salary 0 it will show error.
A DDL trigger is fired in response of DDL statement like drop, create, alter etc.
Create trigger trg_name on Database
For/after/instead of Action(create, drop, alter)
As
Your statements………………………..
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.