?
Solved

Trigger -- based on Process# / Field combo

Posted on 2013-06-28
9
Medium Priority
?
362 Views
Last Modified: 2013-07-02
What is the proper
"after UPDATE" TRIGGER so below works ?

Do you have any freeware tools
to make TRIGGER creation easier.
------------------------------------------
CREATE TRIGGER Test1
   ON  MAINT_WORK_REQ
   AFTER UPDATE
AS
BEGIN

--below syntax is not FULLY correct
    if old.ProcessNumber = 1
      && new.S1Est=new.C1Est,
    then new.ProcessNumber = 6

     else if old.ProcessNumber = 6
       && new.ActionTake IS NOT NULL
       && new.Signature IS NOT NULL
    then new.ProcessNumber = 7
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
  • 3
  • 3
9 Comments
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 1336 total points
ID: 39285561
You may not be able to do that in an AFTER UPDATE trigger because the "old" data is already gone.   You may need to do it in an INSTEAD OF UPDATE trigger.

... in which case, assuming that S1Est is a key on the table,  the syntax would be something like this:


   update M
      set ProcessNumber = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.S1Est = M.S1Est
   where M.ProcessNumber = 1


   update M
      set ProcessNumber = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.S1Est = M.S1Est
   where M.ProcessNumber = 6
       and INSERTED.ActionTake IS NOT NULL
       and INSERTED.Signature IS NOT NULL
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39285585
The above assumes that S1Est is a key on the table, if this is not the case, please post the name(s) of the primary key column(s) and I will adjust the code accordingly.
0
 

Author Comment

by:finance_teacher
ID: 39285696
ID is the ONLY PK I have.
Please post updated code.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Accepted Solution

by:
knightEknight earned 1336 total points
ID: 39285703
-- again, this is an approximation meant for an INSTEAD OF UPDATE trigger, and based on the information provided.

   update M
      set ProcessNumber = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.ProcessNumber = 1
      and I.S1Est = M.S1Est

   update M
      set ProcessNumber = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.ProcessNumber = 6
       and I.ActionTake IS NOT NULL
       and I.Signature IS NOT NULL
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 total points
ID: 39291063
>> You may not be able to do that in an AFTER UPDATE trigger because the "old" data is already gone. <<

Not true.  The "deleted" pseudotable holds the row as it existed before it was updated.  The actual table itself naturally has the new version of the row for an AFTER trigger (or the "inserted" pseudotable contains the new row(s) as well).



CREATE TRIGGER Test1
   ON dbo.MAINT_WORK_REQ
   AFTER UPDATE
AS

UPDATE MWR
SET ProcessNumber = CASE WHEN d.ProcessNumber = 1 THEN 6 ELSE 7 END
FROM dbo.MAINT_WORK_REQ MWR
INNER JOIN deleted d ON
    d.##key_col## = MRW.##key_col## --<-- put your key col(s) here
WHERE
    (d.ProcessNumber = 1 AND MWR.S1Est = MWR.C1Est) OR
    (d.ProcessNumber = 6 AND MWR.ActionTake IS NOT NULL AND WMR.Signature IS NOT NULL)
0
 

Author Comment

by:finance_teacher
ID: 39291364
Below trigger currently emails only ONE person.

How can I setup so below COMMENTED out
code works, sending to multiple users ?

I just want it easy since I have 10+ WorkFlowStages for each
of my 20+ ProgramID, making 200+ possible statements
   if WorkFlowStage = __
   and ProgramID = ___
   then change WorkFlowStage to ___
   and email multiple people
-------------------------------------------------------

CREATE TRIGGER workflow
ON maint_work_req
      AFTER UPDATE, INSERT
AS
BEGIN

update M
      set WorkFlowStage = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID = 'BUICK'

declare @sub1 varchar(100)
set @sub1 = 'johnDoe@test.com'
            /*
              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'BUICK' + '_EMAIL_6'
              --where RoleName = M.ProjectID + '_EMAIL_6'
              and up.email is NOT NULL
             */
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'SABmail',
           @recipients  = @sub1,
           @body = 'This is now in stage 6.....',
           @subject = 'Automated Success Message';
---------------------------------------------------------------
update M
      set WorkFlowStage = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID <> 'FORD'

declare @sub2 varchar(100)
set @sub2 = 'BobSmith@test.com'
            /*
              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'FORD' + '_EMAIL_7'
              --where RoleName = M.ProjectID + '_EMAIL_7'
              and up.email is NOT NULL
             */
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'SABmail',
           @recipients  = @sub2,
           @body = 'This is now in stage 7.....',
           @subject = 'Automated Success Message';
     
END
GO
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39291608
You can use the OUTPUT clause to get data from the rows that were UPDATEd:

create table #email_table (
    id int not null,
    ...
)

update M
      set WorkFlowStage = 6
OUTPUT M.ID, ... INTO #email_table
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID = 'BUICK'

Then send emails based on the columns in the OUTPUT results.
0
 

Author Comment

by:finance_teacher
ID: 39291658
How can I get "@recipients  = @sub2" to include whatever email address(es) appear in the below ?

              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'FORD' + '_EMAIL_7'
              --where RoleName = M.ProjectID + '_EMAIL_7'
              and up.email is NOT NULL
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39291706
I don't know, I don't know the data in those tables.

I don't know what columns feed from the MAINT_WORK_REQ table to the emails.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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