Prevent Insert Trigger

Question

How can I get this trigger to prevent the insert where the advance is not greater than 0 or less than 100? Thanks.

DROP TRIGGER CheckAdvance;
CREATE OR REPLACE TRIGGER CheckAdvance
BEFORE INSERT OR UPDATE OF advance ON titles
FOR EACH ROW
WHEN (new.advance<0 OR new.advance>100)
BEGIN
dbms_output.put_line('Advance is Invalid.');
END;

Answer 1

"it was a class question."

I had a chat with a university lecturer who taught PL/SQL after I gave a presentation at a conference. My talk was on PL/SQL good practice; one of my slides simply said "Don't use triggers". The lecturer told me he finds such advice difficult to reconcile with the needs of the curriculum. They have to teach their students all the syntax but he admitted that often they set tasks which demanded solutions we wouldn't use when writing software professionally.

This is such a question. The correct approach is to use a check constraint, as Gordon's answer shows. Constraints are more efficient, and idiomatic too. But your teacher wants you to write a trigger, so here is your code, corrected.

CREATE OR REPLACE TRIGGER CheckAdvance
    BEFORE INSERT OR UPDATE OF advance ON titles
    FOR EACH ROW
BEGIN
    IF :new.advance < 0 
    THEN
        raise_application_error(-20000
                , 'Advance cannot be less than zero');
    ELSIF :new.advance > 100
    THEN
        raise_application_error(-20001
                , 'Advance cannot be greater than one hundred.');
    END IF;
END;

Points to note:

1. CREATE OR REPLACE means we can change the trigger code without a preliminary DROP statement.
2. BEGIN and END frame blocks of code such as trigger bodies.
3. Static conditionals are framed with IF ... END IF keywords; WHEN is for exiting loop constructs. Not strictly true, see my update below.
4. Reference table column values with :NEW (and :OLD) keywords - note the colon.
5. Use RAISE_APPLICATION_ERROR to throw an exception; the error number must be in the range -20999 to -20000 which Oracle reserves for user-defined exceptions.
6. Make your error message meaningful: tell your users what they did wrong instead of making them guess.
7. Learn to use indentation to make your code readable. Your future co-workers will thank you for it.

---

@yadipp reminded me that the WHEN clause used in the Seeker's question is valid syntax, so belatedly I'm extending my solution to show how that would look. The most important thing to note is that NEW and OLD aren't treated as bind variables and so don't take a :.

CREATE OR REPLACE TRIGGER CheckAdvance
    BEFORE INSERT OR UPDATE OF advance ON titles
    FOR EACH ROW
    WHEN (new.advance < 0 OR new.advance > 100)
BEGIN
    raise_application_error(-20000
                , 'Advance cannot be less than zero or greater than one hundred.');
END;

(And I tweaked my original solution to show one reason to use IF in the trigger body: to handle different conditions differently.)

Answer 2

You shouldn't use a trigger for this. Oracle (and SQL in general) supports check constraints:

alter table titles
    add constraint chk_titles_advance check (advance > 0 and advance <= 100);