Solved

Microsoft SQL 2005 -- conditional database table trigger

Posted on 2013-11-26
6
438 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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