finance_teacher
asked on
Trigger -- based on Process# / Field combo
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
"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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
ID is the ONLY PK I have.
Please post updated code.
Please post updated code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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
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
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.
I don't know what columns feed from the MAINT_WORK_REQ table to the emails.