How to write Trigger in SQL Server 2005
A trigger is a special kind of stored procedure that responds to specific events. More specifically, it is attached to a Table or View or Database schemas for tracking the operations on them. The key difference between a trigger and a stored procedure is that the former is attached to a table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, whereas a stored procedure executes at any time when it is called.
create table t_EmployeeInformation
employee_ID int identity(1,1) NOT NULL,
employee_Status varchar(10) NULL constraint
DF_t_EmployeeInformation_employee_Status default ('Active')
create procedure p_InsertEmployee
insert into t_EmployeeInformation(employee_Name,employee_EmailID)
The following code snippet shows a simple trigger which fires on an insert to table t_EmployeeInformation.
create trigger tr_InsertEmployee
declare @employee_ID int
select @employee_ID = employee_ID from inserted
set employee_Status = 'Inactive'
where employee_ID != @employee_ID
//Use Procedure to Insert Data
exec p_InsertEmployee 'khan', 'email@example.com'
Trigger will fire for the insert event and change all available employee_Status except the last (inserted) one.
A Rahim Khan