Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

help with trigger

Posted on 2014-08-11
4
Medium Priority
?
362 Views
Last Modified: 2014-09-15
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

0
Comment
Question by:FutureDBA-
  • 2
4 Comments
 

Author Comment

by:FutureDBA-
ID: 40254757
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
 

Author Comment

by:FutureDBA-
ID: 40254761
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40255045
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
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 40255276
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

580 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