Trigger Logic and SPs

[vituja]

Hi All,

I'm new to trigger coding. I'm looking to create a trigger that will fire a few stored procedures when certain conditions are met.

If the columns COMPLETE='Y' and Weekrange like "01/01%" and emplid=@emplid on ORDERS_TBL are true then the following Stored procedures should run:
SP_Test1 @emplid
SP_Test2 @emplid
SP_Test3 @emplid

Is something like this possible? Each of the SPs are massive.

What would the syntax be. Any help would be appreciated. Thanks.

 

[ClaireHCM]

Try the following code

Create trigger tr1 on ORDERS_TBL for insert
as

DECLARE @emplid varchar(20)
select @emplid = emplid from inserted

If (select COMPLETE from inserted) = 'Y' and (select Weekrange from inserted) like '01/01%'
and (select emplid from inserted) = @emplid
exec SP_Test1 @emplid
exec SP_Test2 @emplid
exec SP_Test3 @emplid

 

[vituja]

Excellent! It worked. Thanks for the great help

 

[SQLSister]

NOte claire's code and your stored procedure processing method assume that only one record will ever be updated, a very dangerous assumption in a trigger. YOu may need to wrap this in a cursor or redesign you process to do set-based processing (the preferred approach from an efficiency standpoint).