Home » SQL & PL/SQL » SQL & PL/SQL » stored proc to add data to a new row?
stored proc to add data to a new row? [message #263354] Wed, 29 August 2007 16:45 Go to next message
jeffg
Messages: 5
Registered: August 2007
Location: Boise, ID
Junior Member
Okay, so it looks like I can paste data into the topic I get session error message. If you need to see my current code, let me know and I will type it all in.

What I am trying to do is create a stored procedure that can update columns in an existing row. I want to call this with an after insert trigger to supply data that exists in another table, but the user creating the new row does not have access to.

In our application we ask the user to provide a location and and equipment type. This information then maps to another table that contains vendor information. I want to auto populate the vendor information after the record is saved (inserted) by the application.

When I tried my first attempt I thought I could do all of the updates within the trigger body, but I got an error relating to mutating table data.

I then thought I could strip the update out into a store proc and use the trigger to call the proc.

It seems that I cant call the proc from within the trigger. I am getting an error related to missing parameters.

Anyway, like I said, I probably need to post the code, but I cant figure out how to do that.

TIA

Jeff
Re: stored proc to add data to a new row? [message #263355 is a reply to message #263354] Wed, 29 August 2007 16:46 Go to previous messageGo to next message
jeffg
Messages: 5
Registered: August 2007
Location: Boise, ID
Junior Member
Here is my stored proc spec:
CREATE OR REPLACE PACKAGE retail_dispatch_pkg AS
PROCEDURE update_dispatch_details
END retail_dispatch_pkg;

And my stored proc body:
CREATE OR REPLACE PACKAGE BODY retail_dispatch_pkg AS
PROCEDURE update_dispatch_details AS
t_absdispatchto VARCHAR2,
t_abservprovphone VARCHAR2,
t_abservprovaddress VARCHAR2,
t_abservprovcity VARCHAR2,
t_abservprovstate VARCHAR2,
t_abservprovzip VARCHAR2,
t_absfax VARCHAR2
BEGIN
SELECT a.company
INTO t_absdispatchto
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.unitid = w.location
AND a.eqtype = w.failurecode
WHERE a.TRADE = ‘PM’;

SELECT a.primaryphone
INTO t_abservprovphone
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

SELECT a.address
INTO t_abservprovaddress
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

SELECT a.city
INTO t_abservprovcity
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

SELECT a.state
INTO t_abservprovstate
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

SELECT a.zipcode
INTO t_abservprovzip
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

SELECT a.fax
INTO t_absfax
FROM ABSERVICEPROV a inner join WORKORDER w
ON a.UNITID = w.LOCATION and a.EQTYPE = w.FAILURECODE
WHERE a.TRADE = 'PM';

INSERT INTO workorder(
absdispatchto,
abservprovphone,
abservprovaddress,
abservprovcity,
abservprovstate,
abservprovzip,
absfax
)
VALUES(
t_absdispatchto,
t_abservprovphone,
t_abservprovaddress,
t_abservprovcity,
t_abservprovstate,
t_abservprovzip,
t_absfax
);
END update_dispatch_details;

END

Here is my trigger:
CREATE OR REPLACE TRIGGER retail_pm_dispatch
AFTER INSERT
ON workorder
FOR EACH ROW
WHEN (new.location<’08000’ AND new.pmnum is not null)

BEGIN
EXECUTE retail_dispatch_pkg.update_dispatch_details;
END

Hope that works.

[Updated on: Wed, 29 August 2007 16:50]

Report message to a moderator

Re: stored proc to add data to a new row? [message #263356 is a reply to message #263355] Wed, 29 August 2007 16:54 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
read it before posting

http://www.orafaq.com/forum/t/88153/0/
Re: stored proc to add data to a new row? [message #263357 is a reply to message #263354] Wed, 29 August 2007 16:59 Go to previous messageGo to next message
jeffg
Messages: 5
Registered: August 2007
Location: Boise, ID
Junior Member
Thanks, I think?

If you are telling me to search before posting, I have.

If this is in reply to my origional inability to cut and paste into the thread, I did a search and found the solution. Thanks. Smile

If I am totally missing the boat, please be blunt. Smile

Re: stored proc to add data to a new row? [message #263385 is a reply to message #263354] Wed, 29 August 2007 22:42 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Read the User guide and use code format tags.
Re: stored proc to add data to a new row? [message #263412 is a reply to message #263357] Thu, 30 August 2007 00:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Execute is for executing a procedure from SQL*Plus, not from within PL/SQL. From within PL/SQL, you just "BEGIN your_package_name.your_procedure_name; END;". However, you do not need a procedure and if you want to update columns in the same row, then you should not be inserting another row. What you need is to use "SELECT company, primaryphone, ... INTO :NEW.absdispatchto,
:NEW.abservprovphone, ... FROM ABSERVICEPROV ..." within your trigger code.
Re: stored proc to add data to a new row? [message #263678 is a reply to message #263354] Thu, 30 August 2007 13:34 Go to previous messageGo to next message
jeffg
Messages: 5
Registered: August 2007
Location: Boise, ID
Junior Member
Thank you... that helps a bunch.

Here is what I tried:
CREATE OR REPLACE TRIGGER retail_pm_dispatch
BEFORE INSERT
ON workorder
FOR EACH ROW
WHEN (new.location<'08000'AND new.pmnum is not null AND new.absdispatchto is null)
BEGIN
SELECT a.company, a.primaryphone, a.address, a.city, a.state, a.zipcode, a.fax
INTO :new.absdispatchto, :new.abservprovphone, :new.abservprovaddress, :new.abservprovcity, 
:new.abservprovstate, :new.abservprovzip, :new.absfax
FROM workorder w inner join abserviceprov a
ON w.location = a.unitid AND w.failurecode = a.eqtype
WHERE a.trade = 'PM'
END


This doesnt work. I get the following error:
TRIGGER MAXIMO.RETAIL_PM_DISPATCH
On line: 6
PL/SQL: ORA-00933: SQL command not properly ended

I am pretty sure I need to do something like:
INSERT INTO ????
SELECT ...
FROM ...

but I think this wants a table and doesnt really like the :NEW. variable.

PS. hopefully I have the code formatting right

[Updated on: Thu, 30 August 2007 13:42] by Moderator

Report message to a moderator

Re: stored proc to add data to a new row? [message #263681 is a reply to message #263678] Thu, 30 August 2007 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
; missing at line before the last one.

But you will then get another error.

Regards
Michel
Re: stored proc to add data to a new row? [message #263719 is a reply to message #263412] Thu, 30 August 2007 20:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You need to avoid any unnecessary selects from the table that the trigger is on (workorder) in order to avoiding mutating errors. You need to make all references to values in the workorder table using the :new.column_name syntax, something more like this (untested due to lack of ddl statements and sample data):

CREATE OR REPLACE TRIGGER retail_pm_dispatch
  BEFORE INSERT
  ON workorder
  FOR EACH ROW
  WHEN (new.location<'08000'
    AND new.pmnum is not null 
    AND new.absdispatchto is null)
BEGIN
  SELECT a.company, a.primaryphone, a.address, 
         a.city, a.state, a.zipcode, a.fax
  INTO   :new.absdispatchto, :new.abservprovphone,   
         :new.abservprovaddress, :new.abservprovcity, 
         :new.abservprovstate, :new.abservprovzip, :new.absfax
  FROM   abserviceprov a
  WHERE  a.unitid = :new.location 
  AND    a.eqtype = :new.failurecode 
  AND    a.trade = 'PM';
END retail_pm_dispatch;
/

Re: stored proc to add data to a new row? [message #264068 is a reply to message #263354] Fri, 31 August 2007 11:00 Go to previous message
jeffg
Messages: 5
Registered: August 2007
Location: Boise, ID
Junior Member
without a doubt, you are my hero. This whole :new vs direct reference will take some time to wrap my head around. Looks like I need a bit more practice.

Thanks again.

Jeff
Previous Topic: Execute Immediate error
Next Topic: Trigger for restrictions on Database
Goto Forum:
  


Current Time: Tue Dec 06 12:20:45 CST 2016

Total time taken to generate the page: 0.08992 seconds