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;

Solution 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.)

Solution 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);

Comments

[Tim Biegeleisen]

A trigger cannot change the fact that the insert actually happened. To do that, why don't you use a nice healthy WHERE clause in the insert query?

[Bob Jarvis - Слава Україні]

If this is a design question,Tim's comment above and @GordonLinoff's answer below are both excellent. If, however, this is a class assignment (class assignments often ask students to do things which shouldn't be done, apparently because those teaching these classes have never spent enough time as developers to know good from bad) you can raise an exception in the trigger, catch it in the main body of your code, and ROLLBACK the transaction, thus preventing the insert. Note that this is poor practice.

[jor2]

@BobJarvis Yes it was a class question. I think it may be related to RAISE_APPLICATION_ERROR.

[Bob Jarvis - Слава Україні]

Yep, that sounds like what the instructor is looking for. Keep in mind that this is a Bad Idea - just because you throw an exception does not mean that the transaction will be rolled back (although I believe that's the default behavior if the exception goes unhandled).

[vadipp]

Great answer! Commenting just to note that one actually could use a when clause instead of if then end if in this case. See docs.oracle.com/cd/B19306_01/server.102/b14200/…