• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

Microsoft SQL 2005 -- conditional database table trigger

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
finance_teacher
Asked:
finance_teacher
  • 3
  • 2
2 Solutions
 
Mohit VijayCommented:
use Case when then, it will be good.
0
 
finance_teacherAuthor Commented:
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
 
Mohit VijayCommented:
Whats the error?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
finance_teacherAuthor Commented:
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
 
Anthony PerkinsCommented:
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now