Home » SQL & PL/SQL » SQL & PL/SQL » Trigger is not showing expected behaviour
Trigger is not showing expected behaviour [message #313782] Mon, 14 April 2008 09:35 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi all,

I have written a trigger to update the status when certain conditions are met. 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 :NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS
  OR :NEW.POB_VENDOR_SHIP_ADDRESS_2 <> :OLD.POB_VENDOR_SHIP_ADDRESS_2
  OR :NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID
  OR :NEW.POB_REGION <> :OLD.POB_REGION
  OR :NEW.POB_CITY <> :OLD.POB_CITY
  OR :NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL
  OR :NEW.POB_VENDOR_PAYS_FREIGHT <> :OLD.POB_VENDOR_PAYS_FREIGHT
  OR :NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM 
  OR :NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  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;


The above trigger works when the old value or new value is not null i.e.
If I try to update (for e.g.) POB_DC_END_DTTM to null.

The trigger doesnt update the status to 30.

The same happens the other way around too.
If I try to update (for e.g.) a null value of POB_DC_END_DTTM to
12-MAR-08 08.16.36.477140 AM.

Could you please help me out?

Thanks,

Sharath

Re: Trigger is not showing expected behaviour [message #313786 is a reply to message #313782] Mon, 14 April 2008 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you sure the AND and OR and in correct precedence?
Put parenthesis.

Regards
Michel
Re: Trigger is not showing expected behaviour [message #313788 is a reply to message #313782] Mon, 14 April 2008 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>WHERE POB_VENDOR_CLAIM_ID = iPobVendorClaimID;
I don't see that this will ever be true.
Re: Trigger is not showing expected behaviour [message #313790 is a reply to message #313788] Mon, 14 April 2008 10:07 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
>WHERE POB_VENDOR_CLAIM_ID = iPobVendorClaimID;


This part is working fine.

It is the update that is not working.

I will try to add parentheses and see if it works.
Re: Trigger is not showing expected behaviour [message #313793 is a reply to message #313782] Mon, 14 April 2008 10:22 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

By paranthesis, did you mean like this?

CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM_2 BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iPobVendorClaimID NUMBER;
BEGIN
  IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS)
  OR (:NEW.POB_VENDOR_SHIP_ADDRESS_2 <> :OLD.POB_VENDOR_SHIP_ADDRESS_2)
  OR (:NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID)
  OR (:NEW.POB_REGION <> :OLD.POB_REGION)
  OR (:NEW.POB_CITY <> :OLD.POB_CITY)
  OR (:NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL)
  OR (:NEW.POB_VENDOR_PAYS_FREIGHT <> :OLD.POB_VENDOR_PAYS_FREIGHT)
  OR (:NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM)
  OR (:NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  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;


However, it is still not working.

Sharath
Re: Trigger is not showing expected behaviour [message #313794 is a reply to message #313790] Mon, 14 April 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
This part is working fine.

Wrong with what you posted.
iPobVendorClaimID is not initialize so = is never true.

Regards
Michel
Re: Trigger is not showing expected behaviour [message #313796 is a reply to message #313793] Mon, 14 April 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
hedonist123 wrote on Mon, 14 April 2008 17:22
Michel,

By paranthesis, did you mean like this?

CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM_2 BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iPobVendorClaimID NUMBER;
BEGIN
  IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS)
  OR (:NEW.POB_VENDOR_SHIP_ADDRESS_2 <> :OLD.POB_VENDOR_SHIP_ADDRESS_2)
  OR (:NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID)
  OR (:NEW.POB_REGION <> :OLD.POB_REGION)
  OR (:NEW.POB_CITY <> :OLD.POB_CITY)
  OR (:NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL)
  OR (:NEW.POB_VENDOR_PAYS_FREIGHT <> :OLD.POB_VENDOR_PAYS_FREIGHT)
  OR (:NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM)
  OR (:NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  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;


However, it is still not working.

Sharath


No I didn't mean this, these ones are useless.
Which operator has higher priority AND or OR?

Regards
Michel

Re: Trigger is not showing expected behaviour [message #313799 is a reply to message #313793] Mon, 14 April 2008 10:36 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Parentheses are use to alter the order in which expression pairs are evaluated.
For example
WITH t AS (SELECT 'North' Region, 2000 yr FROM DUAL UNION ALL
           SELECT 'South' , 2000  FROM DUAL UNION ALL
           SELECT 'West' , 2000  FROM DUAL UNION ALL
           SELECT 'North' , 2001  FROM DUAL UNION ALL
           SELECT 'South' , 2001  FROM DUAL UNION ALL
           SELECT 'West' , 2001  FROM DUAL)
SELECT * FROM t
WHERE region = 'North'
   OR region = 'South'
  AND yr = 2000;
  
  WITH t AS (SELECT 'North' Region, 2000 yr FROM DUAL UNION ALL
           SELECT 'South' , 2000  FROM DUAL UNION ALL
           SELECT 'West' , 2000  FROM DUAL UNION ALL
           SELECT 'North' , 2001  FROM DUAL UNION ALL
           SELECT 'South' , 2001  FROM DUAL UNION ALL
           SELECT 'West' , 2001  FROM DUAL)
SELECT * FROM t
WHERE (region = 'North'
   OR  region = 'South')
  AND  yr = 2000;

There are 3 expressions in the where clause. Whenever there are more than 2 expressions, Oracle must decide which ones to evaluate first. This is because Oracle will only evaluate 1 pair of expressions at a time. The AND operator takes precedence over the OR operator therefore with your set of conditions, your are basically saying
(:NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10)
OR and_now_the_rest_of_the OR_expressions

It is more likely (but not definite) that you want something like
IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS
  OR :NEW.POB_VENDOR_SHIP_ADDRESS_2 <> :OLD.POB_VENDOR_SHIP_ADDRESS_2
  OR :NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID
  OR :NEW.POB_REGION <> :OLD.POB_REGION
  OR :NEW.POB_CITY <> :OLD.POB_CITY
  OR :NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL
  OR :NEW.POB_VENDOR_PAYS_FREIGHT <> :OLD.POB_VENDOR_PAYS_FREIGHT
  OR :NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM
  OR :NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10
but of course, only you can answer that logic.
Re: Trigger is not showing expected behaviour [message #313805 is a reply to message #313796] Mon, 14 April 2008 10:53 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM_2 BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iPobVendorClaimID NUMBER;
BEGIN
  IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS
  OR :NEW.POB_VENDOR_SHIP_ADDRESS_2 <> :OLD.POB_VENDOR_SHIP_ADDRESS_2
  OR :NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID
  OR :NEW.POB_REGION <> :OLD.POB_REGION
  OR :NEW.POB_CITY <> :OLD.POB_CITY
  OR :NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL
  OR :NEW.POB_VENDOR_PAYS_FREIGHT <> :OLD.POB_VENDOR_PAYS_FREIGHT
  OR :NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM
  OR :NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND :OLD.POB_VENDOR_CLAIM_STATUS <> 10
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  iPobVendorClaimID := :OLD.POB_VENDOR_CLAIM_ID;
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = iPobVendorClaimID;
  END IF;
END;


Both are of equal priority, if either of the clauses within the bracket are satisfied and if the POB_VENDOR_CLAIM_STATUS is <> 10, it should update the status to 20.

Why wouldnt the variable get initialised.

After the claim status is updated, wont the variable get initialised and the later update statement also gets executed.

All portions of the trigger are working if the new and old value both are not null.

Sharath
Re: Trigger is not showing expected behaviour [message #313806 is a reply to message #313782] Mon, 14 April 2008 11:07 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Issue is resolved.

It is unable to compare null values.

So, I used the nvl function to set null to a desired value and it worked.

Thanks for all the inputs.

Sharath
Previous Topic: getting random results from a procedure
Next Topic: NLS Settings
Goto Forum:
  


Current Time: Thu Feb 06 19:16:16 CST 2025