May 23, 2006 at 10:48 pm
i am using sql 2000. i created an update trigger in one table. for every updates, trigger will send a simple email using xp_sendmail. its working if i do the update inside sql or vb.
we have an application called peregrine service center. its backend is sql 2000. whenever i do the updates inside that apps , the trigger is not being set off.
any ideas why? would appreciate any thoughts/ideas on this.
thanks and regards.
ann
May 25, 2006 at 7:30 am
How does the application authenticate to the database? Perhaps you are authenticating as some other users that does not have execute rights to the xp_sendmail stored procedure.
Robin HC
May 25, 2006 at 5:19 pm
yeah, i got the execute permission error. so what i did was i added the user to the list of users that can access the master database and granted the user an execute permission on xp_sendmail.
it doesnt have any effect. trigger is still not firing off.
May 26, 2006 at 8:44 am
Will you script out your trigger code so I can have a look at it? Also, you could put something simple in the top of the trigger too eliminate xp_sendmail form the equation. Insert a time stamp into a simple table or something. This would answer the question, is the trigger truly NOT firing, or is the problem with xp_sendmail.
May 28, 2006 at 4:55 pm
i've tried just inserting a record in a table and its also doing it. every is being fired off in the trigger if you do the updates inside the website but if you do it inside the 3rd-party apps, its not setting it off. here's the complete code of my trigger:
CREATE TRIGGER [NotificationsMovementActivity] ON [dbo].[cm3ra36]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @checklist_activity varchar(255)
DECLARE @checklist_date datetime
DECLARE @previouschecklist_date datetime
DECLARE @checklist_number varchar(60)
DECLARE @location_name varchar(255)
DECLARE @subject varchar(255)
DECLARE @body varchar(255)
DECLARE @job_type varchar(100)
SET @body = ''
SET @subject = ''
IF UPDATE(trigger_checklist_date)
BEGIN
SELECT @checklist_activity = checklist_activity, @checklist_number = number,@checklist_date = checklist_date FROM INSERTED
SELECT @previouschecklist_date = checklist_date FROM DELETED
SELECT @location_name = location_name,@job_type = job_type FROM cm3rm1 WHERE number = @checklist_number
--for retail
IF @checklist_date IS NOT NULL AND (@job_type = 'Retail') AND @previouschecklist_date IS NULL
BEGIN
IF @checklist_activity = 'BID Appendices Signed Off - Issue to Design and Data Migration Teams'
BEGIN
SET @subject = 'BID Appendices for ' + @location_name + ' (' + @checklist_number + ') Signed Off'
SET @body = 'This is official confirmation that BID Appendices for ' + @location_name + ' (' + @checklist_number + ') have been signed off.'
END
IF @checklist_activity = 'Rollback'
BEGIN
SET @subject = 'Deployment forr ' + @location_name + ' (' + @checklist_number + ') Delayed/Postponed'
SET @body = 'This is official confirmation that deployment for ' + @location_name + ' (' + @checklist_number + ') have been delayed/postponed.'
END
IF (@checklist_activity = 'GO to commence build')
BEGIN
SET @subject = 'Build Phase for ' + @location_name + ' (' + @checklist_number + ') Approved'
SET @body = 'Build GO ahead has now been approved for ' + @location_name + ' (' + @checklist_number + '). Commence relevant build activities.'
END
IF (@checklist_activity = 'Build Branch Server, W/S, Printers - Complete')
BEGIN
SET @subject = 'Build Completed for ' + @location_name + ' (' + @checklist_number + ')'
SET @body = 'All build activities for ' + @location_name + ' (' + @checklist_number + ') have now been completed.'
END
IF (@checklist_activity = 'Testing Complete GO- Ship to site')
BEGIN
SET @subject = 'Ship to ' + @location_name + ' (' + @checklist_number + ') Approved'
SET @body = 'This is official confirmation that ' + @location_name + ' (' + @checklist_number + ') is ready for shipping. Ship all relevant equipment to ' + @location_name + ' (' + @checklist_number + ')'
END
IF (@checklist_activity = 'Shipment Commenced')
BEGIN
SET @subject = 'Shipment for ' + @location_name + ' (' + @checklist_number + ') Has Now Commenced'
SET @body = ''
END
IF (@checklist_activity = 'Go for Deployment (DCC Meeting) Communicated to Site')
BEGIN
SET @subject = @location_name + ' (' + @checklist_number + ') is now Ready for Deployment'
SET @body = ''
END
IF (@checklist_activity = 'GO for users, 12 AM (Day 0 Implementation Checklist Complete)')
BEGIN
SET @subject = 'Day 0 Implementation Activities for ' + @location_name + ' (' + @checklist_number + ') Now Completed'
SET @body = ''
END
IF (@checklist_activity = 'Backup of new server completed')
BEGIN
SET @subject = 'New Server Back-up for ' + @location_name + ' (' + @checklist_number + ') Completed'
SET @body = ''
END
IF (@checklist_activity = 'PIR (Equipment Collect from site)')
BEGIN
SET @subject = 'Equipment for ' + @location_name + ' (' + @checklist_number + ') Has Now Been Collected'
SET @body = ''
END
EXEC master.dbo.xp_sendmail 'grp-Service-Desk-Support@cdm.com.au',@body,'','','','',@subject
END
--for HOBO
IF @checklist_date IS NOT NULL AND (@job_type = 'HOBO') AND @previouschecklist_date IS NULL
BEGIN
IF @checklist_activity = 'BID Appendices Signed Off - Issue to Design and Data Migration Teams'
BEGIN
SET @subject = 'BID Appendices for ' + @location_name + ' (' + @checklist_number + ') Signed Off'
SET @body = 'This is official confirmation that BID Appendices for ' + @location_name + ' (' + @checklist_number + ') have been signed off.'
END
IF @checklist_activity = 'Rollback'
BEGIN
SET @subject = 'Deployment forr ' + @location_name + ' (' + @checklist_number + ') Delayed/Postponed'
SET @body = 'This is official confirmation that deployment for ' + @location_name + ' (' + @checklist_number + ') have been delayed/postponed.'
END
IF (@checklist_activity = 'Application Assessment Report produced (first draft)')
BEGIN
SET @subject = 'Application Assessment Report for ' + @location_name + ' (' + @checklist_number + ') Produced'
SET @body = ''
END
IF (@checklist_activity = 'Issue Signed Off BID Main Body')
BEGIN
SET @subject = 'BID Main Body for ' + @location_name + ' (' + @checklist_number + ') Signed Off'
SET @body = 'This is official confirmation that the BID Main Body for ' + @location_name + ' (' + @checklist_number + ') has been signed off.'
END
IF (@checklist_activity = 'Advisor Central machines identified and report issued to AC Support Team')
BEGIN
SET @subject = 'Advisor Central Machines for ' + @location_name + ' (' + @checklist_number + ') Identified'
SET @body = 'The following Advisor Central Machines for ' + @location_name + ' (' + @checklist_number + ') have been identified. The machines are:'
END
IF (@checklist_activity = 'BUE Design Document Complete')
BEGIN
SET @subject = 'BUE Design Document for ' + @location_name + ' (' + @checklist_number + ') Completed'
SET @body = 'This is official confirmation that the BUE Design Document for ' + @location_name + ' (' + @checklist_number + ') has now been completed.'
END
IF (@checklist_activity = 'BUE Build in Test Complete')
BEGIN
SET @subject = 'BUE Build (in test) Complete for ' + @location_name + ' (' + @checklist_number + ')'
SET @body = 'This is official confirmation that the BUE Build (in test) Complete for ' + @location_name + ' (' + @checklist_number + ') has now been completed.'
END
IF (@checklist_activity = 'BUE Testing Complete (in Test)')
BEGIN
SET @subject = 'BUE Testing (in test) for ' + @location_name + ' (' + @checklist_number + ') Completed'
SET @body = 'This is official confirmation that BUE Testing (in test) for ' + @location_name + ' (' + @checklist_number + ') has now been completed.'
END
IF (@checklist_activity = 'Complete Kiosk Production Testing - minimum 10% of users verified')
BEGIN
SET @subject = 'Kiosk Production Testing for ' + @location_name + ' (' + @checklist_number + ') Completed'
SET @body = 'This is official confirmation that Kiosk Production Testing for ' + @location_name + ' (' + @checklist_number + ') has now been completed.'
END
IF (@checklist_activity = 'Go / No Go Decision - Daily Meeting')
BEGIN
SET @subject = 'Go/No Go Decision for ' + @location_name + ' (' + @checklist_number + ')'
SET @body = @location_name + ' (' + @checklist_number + ') is now ready for deployment.'
END
IF (@checklist_activity = 'Execute Implementation Checklist - Complete')
BEGIN
SET @subject = 'Go/No Go Decision for ' + @location_name + ' (' + @checklist_number + ')'
SET @body = @location_name + ' (' + @checklist_number + ') is now ready for deployment.'
END
EXEC master.dbo.xp_sendmail 'grp-Service-Desk-Support@cdm.com.au',@body,'','','','',@subject
END
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply