Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

trigger on insert

Posted on 2014-02-19
17
Medium Priority
?
458 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 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 77

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 77

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 77

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
 

Author Comment

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

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 77

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 77

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 1000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 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