Solved

trigger on insert

Posted on 2014-02-19
17
453 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 77

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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Include a logo in email body using Oracle utl_mail 11 52
SQL query for highest sequence 4 59
Procedure syntax 5 48
join actual table rows based on the column 25 30
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.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

713 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