Solved

trigger on insert

Posted on 2014-02-19
17
448 Views
Last Modified: 2014-02-21
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
Comment
Question by:FutureDBA-
17 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39872268
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872274
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
 

Author Comment

by:FutureDBA-
ID: 39872286
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872294
>>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
 

Author Comment

by:FutureDBA-
ID: 39872305
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
 

Author Comment

by:FutureDBA-
ID: 39872312
nevermind, on the last question, i tested it. worked perfect. thank you.
0
 

Author Comment

by:FutureDBA-
ID: 39872317
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872319
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:FutureDBA-
ID: 39872321
I misspoke, I meant for that Row or all rows in the table
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872327
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872330
>>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
 

Author Comment

by:FutureDBA-
ID: 39872339
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
 

Author Comment

by:FutureDBA-
ID: 39872481
have tried playing with it for the last few hours. still no where
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39872733
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39873328
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
 
LVL 32

Accepted Solution

by:
awking00 earned 250 total points
ID: 39873918
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
 

Author Closing Comment

by:FutureDBA-
ID: 39878612
thanks to both of you. i will be able to use both techniques on this app going forward.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
Need help with Oracle syntax 4 56
Oracle regular expression 6 47
ORA-00923: FROM keyword not found where expected 3 34
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now