Solved

Trigger -- based on Process# / Field combo

Posted on 2013-06-28
9
350 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 334 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
 
LVL 33

Accepted Solution

by:
knightEknight earned 334 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 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:ScottPletcher
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:ScottPletcher
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

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
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.

895 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

15 Experts available now in Live!

Get 1:1 Help Now