Home » SQL & PL/SQL » SQL & PL/SQL » Select query within trigger
Select query within trigger [message #314353] Wed, 16 April 2008 07:44 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I wish to know if it is possible to select records within a trigger.

I wish to execute a trigger that will update only when a value in a column is part of the records that have been selected.

I have read that select queries are not possible within triggers.

Could you please let me know if there is a way out of this?

Sharath
Re: Select query within trigger [message #314355 is a reply to message #314353] Wed, 16 April 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
hedonist123 wrote on Wed, 16 April 2008 08:44
Hi,

I wish to know if it is possible to select records within a trigger.

I wish to execute a trigger that will update only when a value in a column is part of the records that have been selected.

I have read that select queries are not possible within triggers.



Untrue. Why don't you try it yourself and see?
If you are talking about a mutating trigger, then you will encounter this if you try to select from the SAME TABLE. However, there is no need to do that from why I "tried" to understand from your cryptic message. Please explain with an example of what you are trying to do.
Re: Select query within trigger [message #314399 is a reply to message #314355] Wed, 16 April 2008 10:41 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Joy,

Sorry for the incomplete post.

This is the code.

CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM_2 BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iPobVendorClaimID NUMBER;
BEGIN
  IF (NVL(:NEW.POB_VENDOR_SHIP_ADDRESS,'0') <> NVL(:OLD.POB_VENDOR_SHIP_ADDRESS,'0')
  OR NVL(:NEW.POB_VENDOR_SHIP_ADDRESS_2,'0') <> NVL(:OLD.POB_VENDOR_SHIP_ADDRESS_2,'0')
  OR NVL(:NEW.VLD_COUNTRY_ID,0) <> NVL(:OLD.VLD_COUNTRY_ID,0)
  OR NVL(:NEW.POB_REGION,'0') <> NVL(:OLD.POB_REGION,'0')
  OR NVL(:NEW.POB_CITY,'0') <> NVL(:OLD.POB_CITY,'0')
  OR NVL(:NEW.POB_SHIP_POSTAL,'0') <> NVL(:OLD.POB_SHIP_POSTAL,'0')
  OR NVL(:NEW.POB_VENDOR_PAYS_FREIGHT,2) <> NVL(:OLD.POB_VENDOR_PAYS_FREIGHT,2)
  OR NVL(:NEW.POB_DC_END_DTTM,'07-MAR-23 12.00.00.000000 AM') <> NVL(:OLD.POB_DC_END_DTTM,'07-MAR-23 12.00.00.000000 AM')
  OR NVL(:NEW.POB_CLAIM_TOTAL,0.00000005) <> NVL(:OLD.POB_CLAIM_TOTAL,0.00000005))
  AND (:OLD.POB_VENDOR_CLAIM_STATUS <> 10)
  AND :NEW.LAST_UPDATED_SOURCE IN (SELECT 
                                     POB_USR_ID 
                                   FROM
                                     POB_USR PU,
                                     POB_CHANNEL PC
                                   WHERE
                                     PU.POB_CHANNEL_ID = PC.POB_CHANNEL_ID AND
                                     PC.CHANNEL_ID IN (1,5)  
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  :NEW.POB_EMAIL_SENT := 0;
  iPobVendorClaimID := :NEW.POB_VENDOR_CLAIM_ID;
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = iPobVendorClaimID;
  END IF;
END;


It throws error PLS-00405 subquery not allowed in this context

Thanks,

Sharath
Re: Select query within trigger [message #314400 is a reply to message #314353] Wed, 16 April 2008 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It appears you need to modify the code to eliminate the error.
Re: Select query within trigger [message #314409 is a reply to message #314400] Wed, 16 April 2008 11:02 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Could you please advise me on what modifications I have to make?
Re: Select query within trigger [message #314418 is a reply to message #314409] Wed, 16 April 2008 11:41 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a select in IF.

Regards
Michel
Previous Topic: Is it possible to trigger a capture data for v$sql or v$sql_bind_capture with following conditions?
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Sat Dec 10 12:41:45 CST 2016

Total time taken to generate the page: 0.25710 seconds