Solved

trigger on insert

Posted on 2014-02-19
17
447 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
Comment Utility
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)
Comment Utility
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-
Comment Utility
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)
Comment Utility
>>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-
Comment Utility
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-
Comment Utility
nevermind, on the last question, i tested it. worked perfect. thank you.
0
 

Author Comment

by:FutureDBA-
Comment Utility
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)
Comment Utility
>>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-
Comment Utility
I misspoke, I meant for that Row or all rows in the table
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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)
Comment Utility
>>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-
Comment Utility
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-
Comment Utility
have tried playing with it for the last few hours. still no where
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
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)
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 250 total points
Comment Utility
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-
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query 15 62
Out of Sequence numbers for today 25 47
Oracle TEXT search question 9 26
Insert and update a row at the same time 4 30
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

772 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

10 Experts available now in Live!

Get 1:1 Help Now