Proc within a Trigger

Question

Proc within a Trigger

steven.wood

Valued Member

Points: 53
More actions
June 16, 2009 at 7:41 am

#73219

Hi,
Please can someone help as I'm an utter novice when it comes with working with Triggers.
I have a holding table that is populated by a trigger no problem, thing is I'd like another trigger that will fire upon insert that will.....
1, execute the stored procedure
2, delete the record from the holding table
the result being that the holding table will only process one record at a time (as it is populated one record at a time) and will only ever hold a record for processing.
Does anyone have any tips or can point me in the right direction to find out how to do this?
Cheers

Viewing 8 posts - 1 through 7 (of 7 total)

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

steven.wood (6/16/2009)
Hi,
Please can someone help as I'm an utter novice when it comes with working with Triggers.
I have a holding table that is populated by a trigger no problem, thing is I'd like another trigger that will fire upon insert that will.....
1, execute the stored procedure
2, delete the record from the holding table
the result being that the holding table will only process one record at a time (as it is populated one record at a time) and will only ever hold a record for processing.
Does anyone have any tips or can point me in the right direction to find out how to do this?
Cheers

1- First, why do you need to have a holding table, and why do you want to process rows 1 at a time? You have to know triggers have to build for many rows inserts/delete at once, and your code has to handle the multi-rows.

2- You can't execute a procedure from a trigger, you simply can't, so you will need to add the code you want directly in the trigger code.

3- Please provide some table information and some ready to execute sample data, as with a complete explanation of what you want to do, and we can help you build a trigger.

Cheers,

J-F

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

J-F Bergeron (6/16/2009)
2- You can't execute a procedure from a trigger, you simply can't, so you will need to add the code you want directly in the trigger code.

?????

A proc can't be called from within a function. No such restrictions on triggers. It's often a bad idea, but there's no restriction preventing it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steven.wood Valued Member Points: 53 More actions · Answer 3

J-F cheers for the quick response.

Ok a bit of context for you, I have a 3rd party software app I cannot change the database structure of, data from this app needs to be cascaded to 'in house' applications.

So what I have done is create a trigger that will insert an empid in to a holding table (table 1) when a record is updated in the 3rd party app. What I'm then hoping for is another trigger to fire to copy all the data from the 3rd party app in to the 'in house' app tables (table 2), it will update if the record is already in table table 2.

This where the procs comes in. Using the empid as a variable.

Once the proc has finished, the record in the holding table can be deleted.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

GilaMonster (6/16/2009)
J-F Bergeron (6/16/2009)
2- You can't execute a procedure from a trigger, you simply can't, so you will need to add the code you want directly in the trigger code.
?????
A proc can't be called from within a function. No such restrictions on triggers. It's often a bad idea, but there's no restriction preventing it.

Geez, Thanks for the correction Gail, I mislead the OP on this affirmation. I often mistake between "Can not" and "Really should not". I knew you couldn't do it for functions, but I thought it was the same for Procs.

Thanks for the clarification.

Cheers,

J-F

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

steven.wood (6/16/2009)
J-F cheers for the quick response.
Ok a bit of context for you, I have a 3rd party software app I cannot change the database structure of, data from this app needs to be cascaded to 'in house' applications.
So what I have done is create a trigger that will insert an empid in to a holding table (table 1) when a record is updated in the 3rd party app. What I'm then hoping for is another trigger to fire to copy all the data from the 3rd party app in to the 'in house' app tables (table 2), it will update if the record is already in table table 2.
This where the procs comes in. Using the empid as a variable.
Once the proc has finished, the record in the holding table can be deleted.

Steven, from what I can understand, you need to insert in a "House Table" when there is a modification in the 3rd party table.

Why don't you just do it directly? With a trigger on update on the 3rd party table, that will take every record from the "inserted table", and insert them in your "House table"?

Cheers,

J-F

nigelrivett SSCertifiable Points: 5472 More actions · Answer 6

I wouldn't do the update of your database from the trigger. The trigger will hold a transaction and if something is being run on your database that blocks the update then the application will be stuck.

Create audit trail tables to log updates - populate this from the triggers.

You can then use this to update your destination tables via a scheduled job.

The audit trail tables will need an identity to order the updates.

You may also need to have a control table into which is placed the id and table id if referential integrity (update order) is important.

In this way your destination database should not have much impact on the source system.

You could also consider replication or log shipping.

Cursors never.
DTS - only when needed and never to control.

steven.wood Valued Member Points: 53 More actions · Answer 7

nigelrivett (6/16/2009)
I wouldn't do the update of your database from the trigger. The trigger will hold a transaction and if something is being run on your database that blocks the update then the application will be stuck.
Create audit trail tables to log updates - populate this from the triggers.
You can then use this to update your destination tables via a scheduled job.
The audit trail tables will need an identity to order the updates.
You may also need to have a control table into which is placed the id and table id if referential integrity (update order) is important.
In this way your destination database should not have much impact on the source system.
You could also consider replication or log shipping.

Your approach with the scheduled job is exactly how I plan to tackle it, thanks Nige (hope that's ok.) Will just be a cursor to pass in the empids in the holding table, going to be scheduled to run at frequent intervals so wont have to go through many records at a time.