| Trigger Problem [message #38377] |
Tue, 16 April 2002 02:40  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
I have procedure one that wishes to process data into table1 then kick off a trigger that will call a procedure 2. I cant call the procedure direct from procedure one as the procedure 2 has to also be available for calling via another facility so im looking for the trigger to always be called whenever a certain field in table 1 is updated regardless of whichever facility is used to do so. The problem i am having is the trigger calls the procedure with correct parameters but the procedure falls over when it tries to insert data into another seperate table. All permissions are correct and no link exist between any procedure 1 tables and procedure 2 except i wish to copy data from procedure 1 table for use in procedure 2. Surely triggers allow me to call procedures which have inserts within them?? Am i missing something?
Any help would be appreciated,
Thanks
Paul.
|
|
|
|
| Re: Trigger Problem [message #38399 is a reply to message #38377] |
Tue, 16 April 2002 17:30  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Paul, telling us that "the procedure falls over" hardly gives us enough information to help you. What is the exact error message you are getting?
|
|
|
|
| Re: Trigger Problem [message #38409 is a reply to message #38399] |
Wed, 17 April 2002 00:37 |
Paul Healy
Messages: 2
Registered: February 2002
|
Junior Member |
|
|
The problem is thus -
Procedure stage_1 runs and finally populates a table named x.
On populating field y in table x we wish a trigger to be executed which calls a procedure named stage2.
Stage 2 procedures initial processing calls for making a copy of table x data for use in severe manipulation. For this i have a set_up_copy procedure. Table x data should never ever be changed in table x hence need for copy.
The problem is that when i try and copy the data in my set up table procedure it stops when it tries to insert the copy of the data into the copy table. It will do an initial delete but never inserts any data but the procedure simply returns and then the stage2 procedure stops as it belives no data is there for loading into work cursor.
However if i call the set up copy procedure before the trigger ( ie place it in stage1 processing before updating field y) is fired then the data is copied correctly and stage 2 processing can take place. But an additional problem is that the stage 2 process involves recurssion which in turn calls for copying of data. This means that i NEED to be able to have set up copy procedure correctly executed within stage 2 but for some reason it wont execute when process 2 is called by a trigger. If I run stage2 as a stand alone procedure then everything works perfectly and set up copy works fine when called.
This is very desperate can anyone help??
|
|
|
|
| Re: Trigger Problem [message #38441 is a reply to message #38399] |
Wed, 17 April 2002 18:12 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
It's still a bit fuzzy. When you populate column y in table x, the trigger calls stage2 - but then do you copy _all_ of x to a separate table or just the row that you updated? And you want this to happen for every row in x where y is updated?
Can you post some code here? Strip out any unessential parts, but seeing part of the trigger and the procs would help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
