finance_teacher
asked on
Microsoft SQL 2005 -- conditional database table trigger
How can I change the below --> @if INSERTED.ProjectID = 'TEST' then run below else got to next database trigger (workflow3, 4, 5, 6....) ?
Basically I want to have 5+ database triggers using something like the below "AFTER UPDATE, INSERT" logic.
** if ProjectID = 'TEST' then only run MAINT_WORK_REQ table's workflow2 trigger
** if ProjectID = 'TEST3' then only run MAINT_WORK_REQ table's workflow3 trigger
** if ProjectID = 'TEST4' then only run MAINT_WORK_REQ table's workflow4 trigger
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
CREATE TRIGGER [dbo].[workflow2]
ON [dbo].[MAINT_WORK_REQ]
AFTER UPDATE, INSERT
AS
BEGIN
@if INSERTED.ProjectID = 'TEST'
then run below
else got to next database trigger (workflow3, 4, 5, 6....)
update M1
set ActivitySequence = '1'
from MAINT_WORK_REQ M1
join INSERTED I
on I.ID = M1.ID
....... more functions here ..........
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail',
@recipients = 'user@user.com',
@blind_copy_recipients = 'user@user.comm',
@body = 'test',
@body_format = 'HTML',
@subject = 'test';
END
Basically I want to have 5+ database triggers using something like the below "AFTER UPDATE, INSERT" logic.
** if ProjectID = 'TEST' then only run MAINT_WORK_REQ table's workflow2 trigger
** if ProjectID = 'TEST3' then only run MAINT_WORK_REQ table's workflow3 trigger
** if ProjectID = 'TEST4' then only run MAINT_WORK_REQ table's workflow4 trigger
--------------------------
CREATE TRIGGER [dbo].[workflow2]
ON [dbo].[MAINT_WORK_REQ]
AFTER UPDATE, INSERT
AS
BEGIN
@if INSERTED.ProjectID = 'TEST'
then run below
else got to next database trigger (workflow3, 4, 5, 6....)
update M1
set ActivitySequence = '1'
from MAINT_WORK_REQ M1
join INSERTED I
on I.ID = M1.ID
....... more functions here ..........
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail',
@recipients = 'user@user.com',
@blind_copy_recipients = 'user@user.comm',
@body = 'test',
@body_format = 'HTML',
@subject = 'test';
END
use Case when then, it will be good.
ASKER
What is the proper syntax since below fails ?
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
CREATE TRIGGER [dbo].[workflow]
ON [dbo].[MAINT_WORK_REQ]
AFTER UPDATE, INSERT
AS
BEGIN
SELECT
CASE
WHEN ProjectID = 'TEST' THEN exec [MAINT_WORK_REQ].TRIGGER.W ORKFLOW2
WHEN ProjectID = 'TEST3' THEN exec [MAINT_WORK_REQ].TRIGGER.W ORKFLOW3
END
FROM INSERTED
....... more functions here ..........
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail',
@recipients = 'user@user.com',
@blind_copy_recipients = 'user@user.comm',
@body = 'test',
@body_format = 'HTML',
@subject = 'test';
END
--------------------------
CREATE TRIGGER [dbo].[workflow]
ON [dbo].[MAINT_WORK_REQ]
AFTER UPDATE, INSERT
AS
BEGIN
SELECT
CASE
WHEN ProjectID = 'TEST' THEN exec [MAINT_WORK_REQ].TRIGGER.W
WHEN ProjectID = 'TEST3' THEN exec [MAINT_WORK_REQ].TRIGGER.W
END
FROM INSERTED
....... more functions here ..........
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail',
@recipients = 'user@user.com',
@blind_copy_recipients = 'user@user.comm',
@body = 'test',
@body_format = 'HTML',
@subject = 'test';
END
Whats the error?
ASKER
I don't have the correct SYNTAX / etc.
Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'exec'.
Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'TRIGGER'.
Msg 156, Level 15, State 1, Procedure workflow, Line 11
Incorrect syntax near the keyword 'TRIGGER'.
Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'exec'.
Msg 156, Level 15, State 1, Procedure workflow, Line 10
Incorrect syntax near the keyword 'TRIGGER'.
Msg 156, Level 15, State 1, Procedure workflow, Line 11
Incorrect syntax near the keyword 'TRIGGER'.
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.