Selecting from table triggering trigger.

From: Niall Smart <njs3_at_doc.ic.ac.uk>
Date: 1996/08/18
Message-ID: <4v50rr$irf_at_dub-news-svc-6.compuserve.com>#1/1

Dear Reader,

If you don't want to read all of this message: basically I am asking if you can/how to select from the table that triggered the trigger?

I am in the process of trying to learning PL/SQL, specifically with regard to triggers. I have created two tables to play around with:

CREATE TABLE TBL_EMPLOYEES (

	EMP_ID 		NUMBER(6,0)	NOT NULL,	/* Unique ID */
	NAME 		VARCHAR(50)	NOT NULL,	/* Employees Name */
	CONSTRAINT CNS_TBL_EMPLOYEES_0 PRIMARY KEY (EMP_ID)

);

CREATE TABLE TBL_JOBS (

	JOB_ID		NUMBER(6,0)	NOT NULL,	/* Unique ID */
	EMP_ID		NUMBER(6,0)	NOT NULL,	/* ID of the person with this job */

	SALARY		NUMBER(20,0),			/* Salary in USD */
	JOB_TITLE	VARCHAR(50).			/* Official title */
	EFF_DATE	DATE		NOT NULL,	/* Effective Date */
	CONSTRAINT CNS_TBL_JOBS_0 PRIMARY KEY (JOB_ID),
	CONSTRAINT CNS_TBL_JOBS_2 FOREIGN KEY (EMP_ID) REFERENCES

BL_EMPLOYEES(EMP_ID)
);

OK, each employee has one or more corresponding records in TBL_JOBS, the current job record is the record with the maximum effective date (EFF_DATE) less than SYSDATE. The other records are kept in case someone asks what Joe Bloggs' job was back in '69. Anyway, to get someones current job title and salary you need a correlated subquery:

SELECT

	EE.NAME,
	JB.JOB_TITLE,
	JB.SALARY
FROM 
	TBL_EMPLOYEES EE,
	TBL_JOBS JB
WHERE
	JB.EMP_ID = EE.EMP_ID AND
	JB.EFF_DATE = (SELECT MAX(Q.EFF_DATE) FROM TBL_JOBS Q WHERE
				Q.EMP_ID = JB.EMP_ID AND
				Q.EFF_DATE <= SYSDATE) AND

Imagine this is causing big performance problems. So I create a table like this, which holds the ROWID of the current (i.e. active) records in TBL_JOBS:

CREATE TABLE TBL_JOBS_ROWIDS (

	JOB_ROWID	ROWID		NOT NULL,	/* ROWID of current job record */
	CONSTRAINT CNS_TBL_JOB_ROWIDS_0 PRIMARY KEY (JOB_ROWID)

);

So now when I need everyones (current) salary and title I execute this query:

SELECT

	EE.NAME,
	JB.JOB_TITLE,
	JB.SALARY
FROM
	TBL_EMPLOYEES EE,
	TBL_JOBS JB
WHERE
	JB.EMP_ID = EE.EMP_ID AND
	JB.ROWID IN (SELECT * FROM TBL_JOBS_ROWIDS)

Naturally enough, the TBL_JOBS_ROWIDS table is updated with a trigger:

CREATE OR REPLACE TRIGGER TRG_UPDATE_TBL_JOBS_ROWIDS AFTER INSERT OR UPDATE OR DELETE ON TBL_JOBS FOR EACH ROW BEGIN

	IF (:OLD.EMP_ID IS NOT NULL) THEN
		DELETE FROM TBL_JOBS_ROWIDS R WHERE R.ROWID = :OLD.ROWID;
	END IF;
	IF (:NEW.EMP_ID IS NOT NULL) THEN
	    UPDATE TBL_JOBS_ROWIDS R SET R.JOB_ROWID = (SELECT J.ROWID FROM
TBL_JOBS J WHERE
														J.EMP_ID = :NEW.EMP_ID AND
														J.EFF_DATE = (SELECT MAX(Q.EFF_DATE) FROM TBL_JOBS Q WHERE
																		Q.EMP_ID = J.EMP_ID AND
																		Q.EFF_DATE <= SYSDATE));
	END IF;

END; However, inserting a record into TBL_JOBS gives:

ORA-04091: table SYSADM.TBL_JOBS is mutating, trigger/function may not see it
ORA-06512: at line 6
ORA-04088: error during execution of trigger 'SYSADM.TRG_UPDATE_TBL_JOBS_ROWIDS' So, at last, does this mean I can't select from the table that triggered the trigger?

Thanks for your time,

Niall. Received on Sun Aug 18 1996 - 00:00:00 CEST