Trigger that pulls additional values & exec SP

Question

Trigger that pulls additional values & exec SP

Jeffery Williams

SSCertifiable

Points: 6441
More actions
July 26, 2005 at 6:28 am

#89217

Ok, I feel stupid here. I have done this before and for some reason just can not think. In fact I believe I had this trigger working and something went South.
Ok here is the gist. A request comes into a table called Provisioning. The Columns that I am interested in (from the Inserted table) are ActionType, CustCounter, and Template.
Moving aware from a 'job' based executable that runs ever few minutes (put in place pre-me) I want to have things more real-time and more dynamic. That said this trigger, upon insert is to set the variables I just listed (as seen below) as well as join a table that has two columns. This table will map a TEMPLATE (from inserted) to a Stored Procedure that must be called..

Lets say on the inserted we have a Template called ACCOUNT, we look at the ProvisionSP table (looking for Template ACCOUNT). If we find it then we grab the ProvisionSP field, containing the name of the Stored Procedure to run and set it to a variable. If the Template is not found (very possible as we will migrate to this over time) then simply return.

The net effect is that if a SP exists for a given process we will call it passing the CustCounter (Identifies a specific line item in the subscription table which has all the data for provisioning this resource, a ActionType passed in the variable @Method (Tells us things like: The request is for a ADD, CHANGE, DELETE, DISABLE, etc). The SP will have sections to deal with each method; applying the appropriate business rules based on method.

When calling the Stored Procedures manually (giving a valid CustCounter and Method) they work fine. The Trigger does not stop the Insert however it does not seem to fire either, or if it is the variables are not set so the net effect is nothing. You will notice a couple commented lines at the end used to write to a debug table (to see what variables I am getting). These lines write nothing in both cases which means that my IF statement is not getting a match (and there is a match in the ProvisionSP table).
Can someone lend some direction here?
ALTER Trigger [dbo].[trg_CallProvisioningSPOnInsert_I] ON [dbo].[Provision] FOR INSERT
AS
DECLARE @Template as varchar (50),
@Method as varchar (50),
@ProvisionSP as varchar (75),
@CustCounter as int,
@ITemplate as varchar (50),
@ICustCounter as int,
@IActionType as varchar (50)
SELECT @ITemplate = i.Template
, @IActionType = i.ActionType
, @ICustCounter = i.CustCounter
, @ProvisionSP = psp.ProvisionSP
, @Template = psp.Template
FROM INSERTED as i
JOIN ProvisionSP as psp on psp.Template = @ITemplate
--where @ITemplate in (SELECT DISTINCT Template from ProvisionSP)
--(Tried above line for the hell of it no help, commented)
IF @Template = @ITemplate
BEGIN
set @CustCounter = @ICustCounter
set @Method = @IActionType
exec @ProvisionSP @CustCounter, @Method
--EXEC DebugProvisioning 1111, 'ReqServiceChange' --(Writes to debug)
--EXEC DebugProvisioning @CustCounter, @Method (Debug write with variables)
end
else
return

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

G'day,

I cannot claim to know exactly what the problem might be. However, for testing and debugging purposes, I would probably start by removing the join. Leave your initial select from INSERTED in place. But replace the join with a second select. I am speculating that INSERTED is a pseudo-table, and may nto be allowed to participate in joins because the data has not yet been committed to a real table. Give this a try and let us know what happens.

Hope this helps,

Wayne

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 2

hmmmmm. while checking my post to make sure it wasn't truncated, I also noticed this...

SELECT @ITemplate = i.Template

, @IActionType = i.ActionType

, @ICustCounter = i.CustCounter

, @ProvisionSP = psp.ProvisionSP

, @Template = psp.Template

FROM INSERTED as i

JOIN ProvisionSP as psp on psp.Template = @ITemplate

might work better as

SELECT @ITemplate = i.Template

, @IActionType = i.ActionType

, @ICustCounter = i.CustCounter

, @ProvisionSP = psp.ProvisionSP

, @Template = psp.Template

FROM INSERTED as i

JOIN ProvisionSP as psp on psp.Template = i.Template

Let us know how things work out

Wayne

noeld SSC Guru Points: 96590 More actions · Answer 3

Let me warn you that this sort of code is not good when multiple rows are affected !

* Noel

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

I had fixed this code today, sorry just getting time to get back to the Forum here.

I found that sometimes this helps:

IF @NotThinkingToday = 1

select brain from ass where username=<InsertUserNameHere>;

Else

Dont't ask stupid questions

All is right with the world again.

NoelD, You are correct. This table will not be used for MASS insert however so I don't think there is a problem here, unless I am missing something?

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh