passing variables between triggers and stored procs

[thegiraffe]

Newbie to programming within t-sql but i'm slowly understanding it.

My problem
We are using an external piece of software but (with permission) are deveoping the back end to work more with our needs.
When a new contact record is entered into a table I want it to check for a few things and on result setup records as to next processes for setting up new contact information.

Their software requires us to use their API shells as a unique record ID is setup in the table I need to insert into.

I don't want to make the trigger to complicated so i would like the trigger to check on a field (such as source), and dependant on this i want to run different setup procedures.

so if source = Client, I want to insert into another pending table that the source needs to be acredited for the referal.

For this I need to have a large shell of exec values to insert into the table (thus wanting to keep it in a seperate procedure), so i would like to have a exec shell setup in a stored procedure, from the trigger call this and pass relevent information inserted info to this stored procedure.

So in short, get inserted info from trigger and pass through to stored procedure.

I need to use stored procedures for this as i would want to use the same process from many different triggers.

Any help will be greatly received

Thankyou Dave

Theres nothing worse than an idea when its the only one we have!

 

[alfredp77]

do you know that inside the trigger you can do a lot of things with the "inserted" table?

for example:

create trigger Table1_tr
on Table1
for insert

as

if (select count(1) from inserted)>1
begin
raiserror 100000 'One data at a time please'
rollback tran
end

declare @contractdata1 varchar(20),
@contractdata2 int
--... etc etc (declare all variables that you need
-- to store the values from inserted)

select @contractdata1=data1,
@contractdata2=data2 --... etc etc
from inserted

exec storproc @contractdata1, @contractdata2
if @@error!=0
begin
rollback tran -- rollback all changes
return
end

return

 

[thegiraffe]

I need to create many triggers on a table each checking for different things (or i could whack them in one...) but I need to call the same procedure from many tables for many different reasons with only minimal data differences.

Such as we have many status's of records,
If a record ref BI has been confirmed (trigger) then it needs to create a pending record ref BC.
If a record ref BC has been confirmed (trigger) then it needs to create a pending record ref BO.

The actual call shell that we have to use (as it creates a dynamic recid in the pending table) has many variables that I have predefined such as Date, Time, and other basic details.

there are many processes like this so it'd be nice to be able to set a trigger that says

if ref = BI then
exec updateBrecords (with a customerID and ref)

which then runs the stored procedure with a standard defined call shell but adds the references to customerID and ref(BI,BC,BO) into the procedure.

have tried and failed! Dave

Theres nothing worse than an idea when its the only one we have!

 

[alfredp77]

sorry thegiraffe, i think i don't get what is exactly your problem. if you only want to pass the inserted values to a stored proc, then that's all there is to it, take it from "inserted" , place it in variables and send it as a stored proc parameters.
define the trigger "for insert, update" if you need to trap both insert and update event.
if your stored proc is going to update the same table, don't forget to turn on "Allow nested triggers"

 

[thegiraffe]

Sorry my bad,

What you told me was kind of whet i needed to know, i've just been informed by someone else exactly what i need to do and i've picked that out from your code too (now i know what i need to be looking for in your code, as i said, its all new to me!)

Basically the bit i needed was the

EXEC testProc @recID, @userID

and the first line of the stored proc to be

CREATE PROCEDURE testproc
@recID varchar(5),
@userID varchar(5)
AS
......programmy bit......

Sorry if i didn't get you but thanks, it has helped to see more than one persons code conquering the same task.

Cheers Dave

Theres nothing worse than an idea when its the only one we have!

 

[alfredp77]

it's ok. glad that u found the solution