Solved

Microsoft SQL 2005 -- conditional database table trigger

Posted on 2013-11-26
6
433 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now