Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

trigger on insert

Experts,

These are my requirements.

I have a table, we'll call it

credit_customers.

has these fields

CHAIN            VARCHAR2(5)  
CUSTNO           VARCHAR2(9)  
CUSTNAME         VARCHAR2(42) 
SHIP_STREET      VARCHAR2(3)  
REPNO            VARCHAR2(20) 

Open in new window


Requirement 1:
When a user inserts data into the table, he may enter it in this format.

CHAIN		CUSTNO		CUSTNAME		SHIP_STREET		REPNO
--------------------------------------------------------------------------------------
0		29874		Morning Bagles	                 70			NULL

Open in new window


I Need that everytime data is inserted, for a trigger to update CUSTNAME to include CUSTNO IN THIS FORMAT

CHAIN		CUSTNO		CUSTNAME		SHIP_STREET		REPNO
--------------------------------------------------------------------------------------
0		29874		Morning Bagles(29874)	         70			NULL

Open in new window




Requirement 2,

I have another table called credit_salesrep, with 2 columns that looks like this

SHIP_STREET	REPNO
------------------------
70		JDOE

Open in new window


I need on a trigger (preferebaly the same as requirement 1) to update on credit_customers the REPNO to include the REPNO value of credit_salesrep..

So Ideally, after a customer inserts this data here
CHAIN		CUSTNO		CUSTNAME		SHIP_STREET		REPNO
--------------------------------------------------------------------------------------
0		29874		Morning Bagles	                 70			NULL

Open in new window


the trigger should update that row(and only that row, not the entire table) to look like this

CHAIN		CUSTNO		CUSTNAME		SHIP_STREET		REPNO
--------------------------------------------------------------------------------------
0		29874		Morning Bagles(29874)	         70			JDOE

Open in new window



This should only happen on inserts, not on updates.

Thanks
0
FutureDBA-
Asked:
FutureDBA-
2 Solutions
 
slightwv (䄆 Netminder) Commented:
For #1 something like this (untested, just typed in):

create or replace trigger credit_customers
before insert on credit_customers
for each row
begin
:new.CUSTNAME := :new.CUSTNAME || ' (' || :new.CUSTNO || ')';
end;
/

Open in new window


>>Requirement 2,

Same concept, different table/columns.
0
 
slightwv (䄆 Netminder) Commented:
If you are using 11g or above, I would look at virtual columns.  These allow you to derive columns at select time.  It will keep you from getting into issues where a customer number or name might change.


The online docs talk about virtual columns.  They are talked about here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:676611400346196844
0
 
FutureDBA-Author Commented:
for Requirement 2,

can you give me an example since I am using 2 different tables?

I will look into the Virtual columns now, but I must first study it before i implement it, I'm on the clock to have this working by the AM.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
>>can you give me an example since I am using 2 different tables?

Exact same trigger.  Just change the trigger, table and column names.
0
 
FutureDBA-Author Commented:
create or replace trigger credit_customers
before insert on credit_customers
for each row
begin
:new.CUSTNAME := :new.CUSTNAME || ' (' || :new.CUSTNO || ')';
end;
/

Is this going to do the trigger for that column, or for the entire table?
0
 
FutureDBA-Author Commented:
nevermind, on the last question, i tested it. worked perfect. thank you.
0
 
FutureDBA-Author Commented:
I am still not following on req 2

create or replace trigger CREDIT_CUSTOMERS_REP
before insert on credit_customers
for each row
begin
:new.REPNO:= :credit_salesrep.repno;
end;
/

????
0
 
slightwv (䄆 Netminder) Commented:
>>Is this going to do the trigger for that column, or for the entire table?

Just to answer:
You really cannot insert into a column and not the table so as you phrased the requirement:  no difference.

For an update trigger you can specify the column that needs to be tweaked to fire the trigger.
0
 
FutureDBA-Author Commented:
I misspoke, I meant for that Row or all rows in the table
0
 
slightwv (䄆 Netminder) Commented:
>>I am still not following on req 2

Maybe I'm missing the requirement.  

The only difference I see in the data is custname which is the same string concat in the trigger I posted.  Just different tables and columns.  There is another difference that I missed before, the repno column goes from null to jdoe.  I don't see how you derive that.
0
 
slightwv (䄆 Netminder) Commented:
>>I misspoke, I meant for that Row or all rows in the table

The trigger will fire for every row inserted.

I'm not sure I understand what you are asking?
0
 
FutureDBA-Author Commented:
I meant if it was only the row that was being inserted, or every row in the table.


For requirement 2,

I am updating table 1, based on a value of table 2.

so, I am updating

Credit_customers.repno

with

credit_salesrep.repno

where credit_customers.ship_street = credit_salesrep.ship_street
0
 
FutureDBA-Author Commented:
have tried playing with it for the last few hours. still no where
0
 
SurranoCommented:
Is there a reason you want to update the table instead of using a view?

create view CREDIT_CUSTOMERS_VIEW as
select c.chain, c.custno, c.custname||'('||c.custno||')' custname,
  c.ship_street, s.repno
from CREDIT_CUSTOMERS c 
left outer join CREDIT_SALESREP s on (c.ship_street=s.ship_street);

Open in new window


Note: I assume the ship_street is a unique key in CREDIT_SALESREP table.
0
 
slightwv (䄆 Netminder) Commented:
For requirement 2,

I am updating table 1, based on a value of table 2.

so, I am updating

Credit_customers.repno

with

credit_salesrep.repno

where credit_customers.ship_street = credit_salesrep.ship_street

I missed the second trigger needs data from another table.

As long as you don't try selecting from the same table that fired the trigger, just create a variable and select from the other table into the variable.

Then set :new.column_name to the variable.
0
 
awking00Commented:
Will the following resolve both requirements?
create or replace trigger credit_customers
before insert on credit_customers
for each row
declare
v_rep_no varchar2(10);
begin
select rep_no into v_rep_no
from credit_salesrep
where ship_street = :new.ship_street;
:new.rep_no := v_rep_no;
:new.CUSTNAME := :new.CUSTNAME || ' (' || :new.CUSTNO || ')';
end;
/
0
 
FutureDBA-Author Commented:
thanks to both of you. i will be able to use both techniques on this app going forward.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now