My First Trigger, how to? 1

[lmorley]

HI all,
I need to create a trigger that will email user@domain.com
after insert update or delete on the column (complete) on table (mytable)
and email the column (tn) and (complete) values for the row.
I'm not finding much help on this subject.
Thanks,
Len

 

[prabhakar]

You can do this by using extended stored procedures xp_sendmail, xp_startmail.
This is the sample code.

create trigger sendmail
on my table
for insert, update, delete
as
exec master..xp_sendmail 'xyz@yahoo.com', 'changes happend in database'

this is a sample one only. see the transact sql help
(on triggers, xp_startmail, xp_sendmail) for complete syntax
before doing this you have to configure sql mail in enterprise manager/sqlserver group/yourservername/support service/sql mail

 

[lmorley]

Thanks, I've been able to do up to that.
I need the second part.
"and email the column (tn) and (complete) values for the row."

 

[bitbrain]

Triggers have access to two virtual tables called “inserted” and “deleted”. Use select statements to get at the columns & build string for emailing. Keep in mind that an update is a delete and an add. So old values can be retrieved from deleted and new values from inserted. Following is a code sample from a table that has a “status” column:

SELECT @old_status = (SELECT status FROM deleted)
SELECT @new_status = (SELECT status FROM inserted)

 

[lmorley]

How would you build the trigger statement for this to work.
I haven't been able to.

 

[bitbrain]

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 > &quot; &quot;
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 > &quot; &quot;
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

 

[lmorley]

PitBrain!!!!!
Thanks, Thanks, and more Thanks,
All is well in DBA Land.
I was able to glean from your code what I needed!

 

[bitbrain]

Great, glad things worked out.