Following is a complete trigger that I use. It probably has more than you are looking for but it does the type of thing that you are trying to do.
if exists (select * from sysobjects where id = object_id('dbo.proc_stat_update') and sysstat & 0xf = 8)
drop trigger dbo.proc_stat_update
GO
CREATE TRIGGER proc_stat_update on dbo.ADMIN_PROCESS
FOR UPDATE
AS
declare @old_status varchar (15),
@new_status varchar (15),
@process varchar (30),
@old_start_time datetime,
@new_start_time datetime,
@new_end_time datetime,
@in_process varchar (15),
@complete varchar (15),
@bcp_errors_found char (1),
@load_errors_found char (1),
@proj_mgr_mail_id varchar (100),
@user_err_dist_list varchar (255),
@msg varchar (255),
@subj varchar (255)
SELECT @in_process = 'InProcess'
SELECT @complete = 'Complete'
SELECT @process = (SELECT process FROM inserted)
SELECT @bcp_errors_found = (SELECT bcp_errors_found FROM inserted)
SELECT @load_errors_found = (SELECT load_errors_found FROM inserted)
SELECT @old_status = (SELECT status FROM deleted)
SELECT @new_status = (SELECT status FROM inserted)
SELECT @old_start_time = (SELECT start_time FROM deleted)
SELECT @new_start_time = (SELECT start_time FROM inserted)
SELECT @new_end_time = (SELECT end_time FROM inserted)
/*
Identify new processes & write rec to history
*/
IF ( @old_status <> @in_process
AND @new_status = @in_process)
OR (@new_status = @in_process
AND @old_start_time <> @new_start_time)
BEGIN
INSERT INTO ADMIN_PROCESS_HISTORY
(start_time,
process,
status)
SELECT @new_start_time,
@process,
@in_process
END
/*
Identify successful completion & update history
*/
IF @old_status <> @complete
AND @new_status = @complete
AND ( @bcp_errors_found Is Null
OR @bcp_errors_found = ' ')
AND ( @load_errors_found Is Null
OR @load_errors_found = ' ')
BEGIN
UPDATE ADMIN_PROCESS_HISTORY
SET end_time = @new_end_time,
status = @complete
WHERE start_time = @new_start_time
AND process = @process
/* Send Email confirmation */
SELECT @subj = 'End To End Process ' + @process + ' Completed Successfully'
SELECT @msg = @process + ' finished successfully on ' + rtrim(convert(char,@new_end_time))
SELECT @proj_mgr_mail_id = (SELECT project_mgr_mail_id FROM ADMIN_APP_PROPERTIES WHERE app_id = 'e2e')
IF NOT @proj_mgr_mail_id IS NULL
AND @proj_mgr_mail_id > " "
BEGIN
EXEC master..xp_sendmail @recipients = @proj_mgr_mail_id,
@message = @msg,
@subject = @subj
END
SELECT @user_err_dist_list = (SELECT user_error_dist_list FROM ADMIN_APP_PROPERTIES WHERE app_id = 'e2e')
IF NOT @user_err_dist_list IS NULL
AND @user_err_dist_list > " "
BEGIN
EXEC master..xp_sendmail @recipients = @user_err_dist_list,
@message = @msg,
@subject = @subj
END
END
/*
Identify error condition & update history
*/
IF (NOT @bcp_errors_found Is Null
AND @bcp_errors_found <> ' ')
OR (NOT @load_errors_found Is Null
AND @load_errors_found <> ' ')
BEGIN
UPDATE ADMIN_PROCESS_HISTORY
SET end_time = @new_end_time,
status = @new_status + '-Errors Found'
WHERE start_time = @new_start_time
AND process = @process
END
GO