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.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
Nice explain about trigger.
ReplyDeleteUseful and easy, thanks.
ReplyDelete