Trigger is not showing expected behaviour [message #313782] |
Mon, 14 April 2008 09:35  |
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 #313793 is a reply to message #313782] |
Mon, 14 April 2008 10:22   |
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 #313799 is a reply to message #313793] |
Mon, 14 April 2008 10:36   |
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   |
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
|
|
|
|