What is 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

DML (Data Manipulation Language) Trigger

DDL (Data Definition Language) Trigger

DDL (Data Definition Language) Trigger

A DML trigger is fired in response of DML statement like insert, update, delete etc.

Syntax of DDL Trigger

		
Create trigger trg_name on Table_name
For/after/instead of   Action (insert, update, delete)
As
Your statement…………………………


		

Type of DML Trigger

There are two types of DML Trigger

After /For 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)

Instead 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.

Example of Instead of 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.

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………………………..

		

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.

Email Address

For any query you can send mail at info@techaltum.com
Thanks