Solved

Trigger -- based on Process# / Field combo

Posted on 2013-06-28
9
348 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

21 Experts available now in Live!

Get 1:1 Help Now