Home » SQL & PL/SQL » SQL & PL/SQL » If Statement within Update Trigger (merged 2) 10g
If Statement within Update Trigger (merged 2) 10g [message #407578] Wed, 10 June 2009 10:52 Go to next message
phobia42
Messages: 4
Registered: February 2008
Junior Member
I would have thought this would be easy, but I'm having a heck of a time. I will be updating my Oracle database through an ASP.NET application, and I need to handle null values. I am having difficulty getting ASP.NET to insert null values (ie. it wants to put 0 for a null decimal, and 01/01/0001 for null dates). I have decided to handle it at the database level using a Before Update trigger to check the incoming value, and overwrite it if it should be null. Below is the code I am using, but it is choking on the "END IF" statement. Any suggestions? Thanks.

CREATE OR REPLACE TRIGGER U_Tbl_Control_Track_Risk
BEFORE UPDATE
ON Tbl_Control_Track_Risk
FOR EACH ROW
DECLARE

BEGIN
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN NEW.IBP_CONTROL_OWNER_ID := NULL END IF;
IF :NEW.BUSINESS_CONTROL_OWNER_ID = 0 THEN :NEW.BUSINESS_CONTROL_OWNER_ID := NULL END IF;
IF :NEW.CONTROL_DEADLINE < '01-JAN-1900' THEN :NEW.CONTROL_DEADLINE := NULL END IF;
IF :NEW.DATE_CONTROL_IMPLEMENTED < '01-JAN-1900' THEN :NEW.DATE_CONTROL_IMPLEMENTED := NULL END IF;
END;

Report message to a moderator

Re: If Statement within Update Trigger [message #407581 is a reply to message #407578] Wed, 10 June 2009 10:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but it is choking on the "END IF" statement
try ENDIF instead

Report message to a moderator

Re: If Statement within Update Trigger [message #407582 is a reply to message #407578] Wed, 10 June 2009 11:01 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"I am choking" is not an Oracle error message.

Post the actual error message you get, otherwise nobody will be able to help you.

'01-JAN-1900' is a string, not a date. Use an explicit to_date with a format mask, or there will definitely be problems.

But read the forum guide first, on how to format code in your post.

Report message to a moderator

Re: If Statement within Update Trigger [message #407583 is a reply to message #407581] Wed, 10 June 2009 11:03 Go to previous messageGo to next message
phobia42
Messages: 4
Registered: February 2008
Junior Member
Didn't work, I got the following error:

ERROR line 38, col 76, ending_line 38, ending_col 80, Found 'ENDIF', Expecting: -or- (+) AT DAY MULTISET YEAR -or- ** -or- * / MOD REM -or- + - || -or- -or- ! != < <= <> = > >= ^ ^= IS NOT -or- BETWEEN IN LIKE LIKE2 LIKE4 LIKEC MEMBER SUBMULTISET -or- AND -or- OR -or- ;

Report message to a moderator

Re: If Statement within Update Trigger [message #407584 is a reply to message #407583] Wed, 10 June 2009 11:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Why can't you post the error for the old code?

By
Vamsi

Report message to a moderator

Re: If Statement within Update Trigger [message #407587 is a reply to message #407583] Wed, 10 June 2009 11:10 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Try putting a semicolon after your ":= null" statements

Report message to a moderator

Re: If Statement within Update Trigger (merged 2) 10g [message #407588 is a reply to message #407578] Wed, 10 June 2009 11:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're missing a semi-colon on several lines. Eg
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN NEW.IBP_CONTROL_OWNER_ID := NULL END IF;
should be
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN NEW.IBP_CONTROL_OWNER_ID := NULL; END IF;


Plus, as Thomas says - '01-Jan-1900' is a string.
to_date('01-Jan-1900','dd-mon-yyyy') is a date

[Too slow by seconds.... drat]

[Updated on: Wed, 10 June 2009 11:12]

Report message to a moderator

Re: If Statement within Update Trigger [message #407589 is a reply to message #407584] Wed, 10 June 2009 11:10 Go to previous messageGo to next message
phobia42
Messages: 4
Registered: February 2008
Junior Member
the original had the same error, except instead of saying it found "ENDIF", it says it found "END".

Original Error Message:

ERROR line 38, col 72, ending_line 38, ending_col 74, Found 'END', Expecting: -or- (+) AT DAY MULTISET YEAR -or- ** -or- * / MOD REM -or- + - || -or- -or- ! != < <= <> = > >= ^ ^= IS NOT -or- BETWEEN IN LIKE LIKE2 LIKE4 LIKEC MEMBER SUBMULTISET -or- AND -or- OR -or- ;

Report message to a moderator

Re: If Statement within Update Trigger [message #407590 is a reply to message #407589] Wed, 10 June 2009 11:14 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
ERROR line 38, col 72


Your code doesn't have 38 lines.

Report message to a moderator

Re: If Statement within Update Trigger (merged 2) 10g [message #407592 is a reply to message #407578] Wed, 10 June 2009 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
CREATE OR REPLACE TRIGGER u_tbl_control_track_risk
  BEFORE UPDATE ON tbl_control_track_risk
  FOR EACH ROW
DECLARE
BEGIN
  IF (:NEW.ibp_control_owner_id = 0) THEN
    :NEW.ibp_control_owner_id := NULL;
  END IF;
  
  IF (:NEW.business_control_owner_id = 0) THEN
    :NEW.business_control_owner_id := NULL;
  END IF;
  
  IF (:NEW.control_deadline < '01-JAN-1900') THEN
    :NEW.control_deadline := NULL;
  END IF;
  
  IF (:NEW.date_control_implemented < '01-JAN-1900') THEN
    :NEW.date_control_implemented := NULL;
  END IF;
END;

Report message to a moderator

Re: If Statement within Update Trigger (merged 2) 10g [message #407593 is a reply to message #407588] Wed, 10 June 2009 11:20 Go to previous message
phobia42
Messages: 4
Registered: February 2008
Junior Member
JRowbottom wrote on Wed, 10 June 2009 11:10
You're missing a semi-colon on several lines. Eg
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN NEW.IBP_CONTROL_OWNER_ID := NULL END IF;
should be
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN NEW.IBP_CONTROL_OWNER_ID := NULL; END IF;


Plus, as Thomas says - '01-Jan-1900' is a string.
to_date('01-Jan-1900','dd-mon-yyyy') is a date

[Too slow by seconds.... drat]


That seemed to work. Thanks JRow. Guess my PL/SQL was rustier than I though, lol. The new code that seems to work:

CREATE OR REPLACE TRIGGER U_Tbl_Control_Track_Risk
BEFORE UPDATE
ON Tbl_Control_Track_Risk
FOR EACH ROW
DECLARE

BEGIN
IF :NEW.IBP_CONTROL_OWNER_ID = 0 THEN :NEW.IBP_CONTROL_OWNER_ID := NULL; END IF;
IF :NEW.BUSINESS_CONTROL_OWNER_ID = 0 THEN :NEW.BUSINESS_CONTROL_OWNER_ID := NULL; END IF;
IF :NEW.CONTROL_DEADLINE < TO_DATE('01-Jan-1900','dd-mon-yyyy') THEN :NEW.CONTROL_DEADLINE := NULL; END IF;
IF :NEW.DATE_CONTROL_IMPLEMENTED < TO_DATE('01-Jan-1900','dd-mon-yyyy') THEN :NEW.DATE_CONTROL_IMPLEMENTED := NULL; END IF;
END;

Report message to a moderator

Previous Topic: schema level trigger
Next Topic: Data Migration
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 11:14:28 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.