Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Microsoft SQL 2005 -- conditional database table trigger

Posted on 2013-11-26
6
Medium Priority
?
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 1000 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 1000 total points
ID: 39680956
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 and will be exposed to the many uses the SELECT statement has.

721 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