Using :new.ROWID in Triggers

I am trying to pass the ROWID of the current row to a stored procedure from a trigger:

	DROP TRIGGER EMP_DEPTNO_NN;
	CREATE TRIGGER EMP_DEPTNO_NN
		AFTER INSERT OR UPDATE OF DEPTNO ON EMP
		FOR EACH ROW
	WHEN (new.DEPTNO = 99 AND new.DEPTNO != old.DEPTNO)
	
	BEGIN
		IFACE_QUEUE.ADD_EVENT('EMP.DEPTNO.NN.*', 
				ROWIDTOCHAR(:new.ROWID), '');
	END;
	/
	COMMIT;

The stored procedure inserts the ROWID into a table. The value that is inserted is ''. It appears as though :new.ROWID is NOT returning the ROWID. If I change the stored procedure call with that insert that is done in the stored procedure:

    INSERT INTO IFACE_QEVENT (

		EVENT_ID,
		EVENT_TIME,
		EVENT_STATUS,
		STATUS_TIME,
		EVENT_ROWID,
		EVENT_ARGS
	)
	VALUES (


		'EMP.DEPTNO.NN.*', 


		SYSDATE, 


		'C', 

		SYSDATE,
		ROWIDTOCHAR(:new.ROWID),


		''

	);

A valid ROWID is inserted into the table; however, if you do the following:

    DECLARE
            xx ROWID;
    BEGIN

	    xx := :new.ROWID;
	    INSERT INTO IFACE_QEVENT (
		EVENT_ID,
		EVENT_TIME,
		EVENT_STATUS,
		STATUS_TIME,
		EVENT_ROWID,
		EVENT_ARGS
	)
	VALUES (


		'EMP.DEPTNO.NN.*', 


		SYSDATE, 


		'C', 

		SYSDATE,
		ROWIDTOCHAR(xx),


		''

	);

'' is inserted into EVENT_ROWID.

It appears as though any reference to :new.ROWID will only work in a SQL-type statement (SELECT, UPDATE, INSERT). As Oracle recommends that trigger should call a stored procedure because of the way Oracle stores triggers; I can't believe that this is the expected behavior.

Any way to get this to work with the stored procedure call?

Thanks. Received on Thu Mar 25 1993 - 16:03:30 CET