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

x
?
Solved

Simple query about a database trigger in Oracle

Posted on 2008-10-06
3
Medium Priority
?
594 Views
Last Modified: 2013-12-18
Please explain reason for trigger compilation errors
trigquery.txt
0
Comment
Question by:tony_stockman
[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
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 22649579
This line is the problem...

INSERT INTO EMP_HIST VALUES (EMPNO, ENAME, :OLD.COMM, :NEW.COMM, SYSDATE);


you have referenced the empno and ename columns but haven't specified the :old  or :new...  


Note,  when doing an insert, you don't have an old


I suggest modifying the trigger something like this...

CREATE OR REPLACE TRIGGER audit_emp
    AFTER INSERT OR UPDATE OF comm
    ON emptrig
    FOR EACH ROW
BEGIN
    IF INSERTING
    THEN
        INSERT INTO emp_hist
          VALUES   (
                        :new.empno, :new.ename, NULL, :new.comm, SYSDATE
                   );
    ELSE
        INSERT INTO emp_hist
          VALUES   (
                        :old.empno, :old.ename, :old.comm, :new.comm, SYSDATE
                   );
    END IF;
END;

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.

705 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