help with trigger

I have a table that has a PK for tickets, this table gets populated automatically via a procedure on a schedule.

This table has a BLOB image that I am trying to display on oracle apex via a form on report.

Apex require for me to have either a sequence, trigger or PLSQL block for trigger management..

I started working on a trigger that will update the PK field to the number it already has an also update 3 other fields that I need populated.

This is where I am with the trigger but am having trouble getting it compiled. I really have no need for the trigger because no new records will be created from APEX, but it is a requirement of the Apex frontend to have  PK management in place

CREATE OR REPLACE TRIGGER DRV_ADJ
	BEFORE INSERT ON DRIVER_ADJUSTMENTS FOR EACH ROW
DECLARE
	TICKET    VARCHAR2(20);
  MIMETYPE  VARCHAR2(48);
  FILENAME  VARCHAR2(90);
  CREATED   VARCHAR2(20);
BEGIN
	SELECT 
    TICKET,
    'image/png',
    'SIG_'||TICKET||'.png',
    TO_CHAR(sysdate,'MON-DD-YY') CREATED_ON,
	INTO  TICKET,
        MIMETYPE,
        FILENAME,
        CREATED
FROM DRIVER_ADJUSTMENTS;
:NEW.TICKET := TICKET;
:NEW.MIMETYPE := MIMETYPE;
:NEW.FILENAME := FILENAME;
:NEW.CREATED_ON := CREATED;
END;
/

Open in new window


desc driver_adjustments;
------------ -------- ------------- 
TICKET       NOT NULL VARCHAR2(20)  
CASH                  NUMBER        
CREDITS               NUMBER        
CRATES_IN             NUMBER        
CRATES_OUT            NUMBER        
DELDATE               DATE          
STATUS                VARCHAR2(20)  
SIGNATURE             CLOB          
SIG_BLOB              BLOB          
MIMETYPE              VARCHAR2(48)  
FILENAME              VARCHAR2(90)  
CID                   VARCHAR2(9)   
DOC_SIZE              NUMBER        
CREATED_BY            VARCHAR2(255) 
CREATED_ON            VARCHAR2(20)  
CONTENT_TYPE          VARCHAR2(128) 

Open in new window

FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FutureDBA-Author Commented:
the issue I am having is with the date, when I removed the date references on the trigger and changed it to be on update, the trigger compiles, but when I make changes to the record, the other 2 columns do not get updated

FILENAME and MIMETYPE should be updated, but the columns are still null after an update is made to the row

CREATE OR REPLACE TRIGGER DRV_ADJ
	BEFORE UPDATE ON DRIVER_ADJUSTMENTS FOR EACH ROW
DECLARE
	TICKET    VARCHAR2(20);
  MIMETYPE  VARCHAR2(48);
  FILENAME  VARCHAR2(90);
BEGIN
	SELECT 
    TICKET,
    'image/png',
    'SIG_'||TICKET||'.png'
	INTO  TICKET,
        MIMETYPE,
        FILENAME
FROM DRIVER_ADJUSTMENTS;
:NEW.TICKET := TICKET;
:NEW.MIMETYPE := MIMETYPE;
:NEW.FILENAME := FILENAME;
END;
/

Open in new window

0
FutureDBA-Author Commented:
I get
ORA-04091: table CDC.DRIVER_ADJUSTMENTS is mutating, trigger/function may not see it ORA-06512: at "CDC.DRV_ADJ", line 6 ORA-04088: error during execution of trigger 'CDC.DRV_ADJ'

Open in new window



when i try to update any record on the table
0
Helena Markováprogrammer-analystCommented:
I think that the problem is caused by missing where clause in your trigger.
SELECT
    TICKET,
    'image/png',
    'SIG_'||TICKET||'.png'
      INTO  TICKET,
        MIMETYPE,
        FILENAME
FROM DRIVER_ADJUSTMENTS; -- here
It returns all rows from DRIVER_ADJUSTMENTS table and it results in ORA-0409:
trigger wants update row which was updated.

So add where clause to your trigger.
0
johnsoneSenior Oracle DBACommented:
The mutating table message is because you are selecting from the table that the trigger fires on.  If I understand what you are trying to do, I think it is this simple.

CREATE OR replace TRIGGER drv_adj 
  BEFORE UPDATE ON driver_adjustments 
  FOR EACH ROW 
BEGIN 
    :NEW.ticket := :NEW.ticket; 

    :NEW.mimetype := 'image/png'; 

    :NEW.filename := 'SIG_' 
                     ||:new.ticket 
                     ||'.png'; 
END; 

/ 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.