Solved

Trigger -- based on Process# / Field combo

Posted on 2013-06-28
9
352 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
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.

 
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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: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

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 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