Solved

Simple query about a database trigger in Oracle

Posted on 2008-10-06
3
578 Views
Last Modified: 2013-12-18
Please explain reason for trigger compilation errors
trigquery.txt
0
Comment
Question by:tony_stockman
3 Comments
 
LVL 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL LOOP CURSOR 3 58
Converting some Oracle code. Need help from Oracle expert who knows sql server 2 48
sql query 9 37
Oracle and DateTime math 6 26
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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.

910 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

17 Experts available now in Live!

Get 1:1 Help Now