basic trigger in sql server with example

in this article, i will show you basic trigger in sql server with example

a trigger is a special kind of stored procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs. we cannot explicitly call any trigger.

why need to trigger?

-to logging and store information
-to implement business rules, data integrity
-to prevent invalid transactions

-------dml trigger---------

create table tbl_logempdata
(
log_data varchar(max),
log_date datetime
)

create trigger tr_logdataemp
on employee
for delete
as
begin
declare @empid int,@empname varchar(100),@empsalary decimal,@logdata varchar(max)
select @empid=i.id,@empname=i.name,@empsalary=i.salary from deleted i
set @logdata='an employee deleted id=' + cast(@empid as varchar)+' , name='+@empname+' and ='+cast(@empsalary as varchar)+''
insert into tbl_logempdata values(@logdata,getdate())
end
_______

create trigger tr_suspendday_emp
on employee
for insert,update,delete
as
begin
   if(datepart(dw,getdate()))=1
   begin
      print 'you cannot perform this operation on this day'
      rollback transaction
   end
end

-------ddl trigger----------

create trigger tr_dbsaftey
on database
for
create_table,alter_table,drop_table
as
print 'you can not create, drop and alter table in this database'
rollback;