Triggers in sql

Trigger in SQL

     Triggers could be defined on the table, view, database or schema with the event is associated. If you write a trigger for delete, trigger will execute when the table data's are deleted.

Syntax

create trigger <Trigger_name>
on Table_name
<AFTER,BEFORE> <INSERT,DELETE,UPDATE>
as
declare @ID varchar(20);
select @ID=alice_name.ColumnName from inserted alice_name;

Example

create trigger tIU
on Emp_tb_help_HelpDesk
after UPDATE,insert
as
 declare @HID int; 
 select @HID=i.HelpDeskID  from inserted i;
 insert into Emp_tb_help_HelpDesk_History(HID) values(@HID)

Trigger execute changes on particular column

     If you write a trigger like above method, trigger will execute if any modification on specified table.

Syntax

IF (UPDATE (ColumnName1) OR update(ColumnName2))
begin
  --------------------
  --------------------
End

Example Program

create trigger tIU on Emp_tb_help_HelpDesk after UPDATE,insert
as
 declare @HID int; 
 declare @status varchar(50);
 declare @priority varchar(50);
 
IF (UPDATE (Priority) OR update(Status)) --Priority and Status is the Column Name
begin
 select @HID=i.HelpDeskID,@status=i.status,@priority=i.priority from inserted i;
 insert into Emp_tb_help_HelpDesk_History(HID,status,Priority,InsertedDate) values(@HID,@status,@priority,getdate())
end