| Adding an IF Statement to a Trigger? [message #574229] |
Tue, 08 January 2013 03:34  |
 |
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  |
 |
Michel Cadot
Messages: 54155
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 #574531 is a reply to message #574245] |
Fri, 11 January 2013 14:26 |
Bill B
Messages: 985
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 |
|
Michel Cadot
Messages: 54155
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
