Solved

Simple query about a database trigger in Oracle

Posted on 2008-10-06
3
582 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 50 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

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
Creation date for a PDB 5 86
SQL Workhours Count beetween Workhours 3 50
Help with Oracle IF statment 5 50
Dbms_job.change procedure 16 37
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

751 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