update trigger on sql 2000

  • 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

  • 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

  • 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.

  • 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.

  • 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