[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

after trigger

Posted on 2011-10-31
5
Medium Priority
?
462 Views
Last Modified: 2013-12-18
hi
i'm looking for syntax on using a trigger (unless you have a better solution) to update columnB based on the value of columnA whenever columnA is updated or inserted for new records.
Instead of doing a sql statement on a form action page I'm choosing a trigger because there are other applications that edit data in this table.

thanks
0
Comment
Question by:dgrafx
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 37057405
Try this:

CREATE OR REPLACE TRIGGER mytrig
BEFORE INSERT OR UPDATE OF columnA ON mytable
BEGIN
   :new.columnB := <expression here>;
END;
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 37057419
You don't say how you want to update columnB, but it can be any expression and if you want to refer to columnA, then reference it as :new.columnA in the expression.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 37057503
i get error "Error: ORA-04082: NEW or OLD references not allowed in table level triggers"

thanks
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 2000 total points
ID: 37057558
Okay, try this:

CREATE OR REPLACE TRIGGER mytrig
BEFORE INSERT OR UPDATE OF columnA ON mytable
FOR EACH ROW
BEGIN
   :new.columnB := <expression here>;
END;


I thought the "FOR EACH ROW" clause was optional, but maybe not.
0
 
LVL 25

Author Closing Comment

by:dgrafx
ID: 37057667
ok - looks good and thanks a lot!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month19 days, 12 hours left to enroll

872 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