Home » SQL & PL/SQL » SQL & PL/SQL » Adding an IF Statement to a Trigger? (Oracle SQL Developer)
Adding an IF Statement to a Trigger? [message #574229] Tue, 08 January 2013 03:34 Go to next message
deadanyway
Messages: 2
Registered: January 2013
Location: Aberdeen
Junior Member
I have created the below trigger in Oracle to ensure that a value in a table remains at 180 but I need to restrict this to one row in this table. Is there a way to use an IF statement or a where clause of some type for a particular row in that table? The Row is called ADMIN. Triggers weren't covered in my course so only know a few basic statements.


CREATE OR REPLACE
TRIGGER DAYSAGO_trg
BEFORE UPDATE ON days_ago
FOR EACH ROW
BEGIN
:NEW.days_ago := 180;

END;

Any help or pointers would be greatly appreciated.
Re: Adding an IF Statement to a Trigger? [message #574230 is a reply to message #574229] Tue, 08 January 2013 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

CREATE OR REPLACE TRIGGER DAYSAGO_trg
BEFORE UPDATE ON days_ago
FOR EACH ROW
WHEN  (old.mycol = 'ADMIN')
BEGIN
  :NEW.days_ago := 180;
END;
/


Regards
Michel

[Updated on: Tue, 08 January 2013 03:38]

Report message to a moderator

Re: Adding an IF Statement to a Trigger? [message #574245 is a reply to message #574230] Tue, 08 January 2013 03:50 Go to previous messageGo to next message
deadanyway
Messages: 2
Registered: January 2013
Location: Aberdeen
Junior Member
Excellent, that works like a charm! Thanks very much Michel and thanks for the welcome and tips.
Re: Adding an IF Statement to a Trigger? [message #574531 is a reply to message #574245] Fri, 11 January 2013 14:26 Go to previous messageGo to next message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
Michel,
Just a thought. Your trigger wouldn't work if they made mycol ADMIN on a new record. or inserted a new ADMIN record. How about.

CREATE OR REPLACE TRIGGER DAYSAGO_trg
BEFORE INSERT OR UPDATE ON days_ago, MYCOL
FOR EACH ROW
WHEN  (NEW.mycol = 'ADMIN')
BEGIN
  :NEW.days_ago := 180;
END;
/
Re: Adding an IF Statement to a Trigger? [message #574532 is a reply to message #574531] Fri, 11 January 2013 14:44 Go to previous message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was just the idea of how to do it not a complete solution that depends on a complete specification of the case which did not exist here, we even don't know if there is a "mycol" column or if an "ADMIN" row is based on some other data. I think OP modified the trigger to make it work for his specific case.

Anyway, it was worth to point to this issue for future readers.

Regards
Michel
Previous Topic: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error
Next Topic: Import excel file to table
Goto Forum:
  


Current Time: Fri Sep 19 01:19:22 CDT 2014

Total time taken to generate the page: 0.06791 seconds