Solved

Microsoft SQL 2005 -- conditional database table trigger

Posted on 2013-11-26
6
442 Views
Last Modified: 2013-12-12
How can I change the below  --> @if INSERTED.ProjectID = 'TEST' then run below else got to next database trigger (workflow3, 4, 5, 6....) ?

Basically I want to have 5+ database triggers using something like the below "AFTER UPDATE, INSERT" logic.
   ** if ProjectID = 'TEST' then only run MAINT_WORK_REQ table's workflow2 trigger
   ** if ProjectID = 'TEST3' then only run MAINT_WORK_REQ table's workflow3 trigger
   ** if ProjectID = 'TEST4' then only run MAINT_WORK_REQ table's workflow4 trigger  
------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[workflow2]
ON [dbo].[MAINT_WORK_REQ]
      AFTER UPDATE, INSERT
AS
BEGIN

@if INSERTED.ProjectID = 'TEST'
  then run below
  else got to next database trigger (workflow3, 4, 5, 6....)

            update M1
        set ActivitySequence = '1'
            from MAINT_WORK_REQ M1
            join INSERTED I
            on I.ID = M1.ID
            
            ....... more functions here ..........
   
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'mail',
           @recipients = 'user@user.com',
           @blind_copy_recipients = 'user@user.comm',
           @body = 'test',
           @body_format = 'HTML',
           @subject = 'test';
END
0
Comment
Question by:finance_teacher
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39678665
use Case when then, it will be good.
0
 

Author Comment

by:finance_teacher
ID: 39678689
What is the proper syntax since below fails ?
------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[workflow]
ON [dbo].[MAINT_WORK_REQ]
      AFTER UPDATE, INSERT
AS
BEGIN


SELECT
   CASE
      WHEN ProjectID = 'TEST'  THEN exec [MAINT_WORK_REQ].TRIGGER.WORKFLOW2
      WHEN ProjectID = 'TEST3' THEN exec [MAINT_WORK_REQ].TRIGGER.WORKFLOW3
   END
FROM INSERTED
           
            ....... more functions here ..........
   
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'mail',
           @recipients = 'user@user.com',
           @blind_copy_recipients = 'user@user.comm',
           @body = 'test',
           @body_format = 'HTML',
           @subject = 'test';
END
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39678722
Whats the error?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:finance_teacher
ID: 39678752
I don't have the correct SYNTAX / etc.

Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'exec'.
Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'TRIGGER'.
Msg 156, Level 15, State 1, Procedure workflow, Line 11
Incorrect syntax near the keyword 'TRIGGER'.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 39679632
If you need to execute the same code in a TRIGGER consider placing this code in a Stored Procedure you can then execute it from both the TRIGGERs.  

Something like this:
IF EXISTS (SELECT 1 FROM INSERTED WHERE ProjectID = 'TEST')
         EXEC usp_YourStoredProcedure1GoesHere

IF EXISTS (SELECT 1 FROM INSERTED WHERE ProjectID = 'TEST3')
         EXEC usp_YourStoredProcedure2GoesHere

etc ...

However, I have to warn you, that the code in TRIGGERs should in general be very lightweight and sending emails from a TRIGGER is never a good idea.
0
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 250 total points
ID: 39680956
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question