Trigger to ensure a value cannot happen

Question

This is using Oracle SQL. Apologies in advance as I am new to the SQL world.

I'm trying to create a simple trigger to ensure a sports event cannot happen in a certain month (we'll use December as the example). So if someone tries to insert a new row with a date in December, the trigger will prevent it.

The current table uses the DATE datatype, inserted as 'DD-MMM-YYYY' but when selected it's displayed as 'DD-MMM-YY' (I don't know why.)

Anyway, I've never made triggers before and I've tried it two ways but it bugs out because when I press ENTER on SQL Plus, it just keeps going as if I was missing a semi-colon. And I'm guessing the trigger itself is not working.

CREATE OR REPLACE TRIGGER event_test
BEFORE INSERT OR UPDATE
ON sports_event
BEGIN
    IF DATE
        IS 'DEC' THEN
        'Sports cannot occur during December.';
    END IF;
END;

I've also tried with a CASE and I could not get it to work.

You need to terminate the PL/SQL block with a `/` in SQL\*Plus. See here for a longer explanation: http://stackoverflow.com/a/10207695/330315 One more thing: dates don't have "a format". They are stored in a binary representation. Any "format" you see is applied by the SQL client you are using (in your case SQL\*Plus). — , Jan 03 '15 at 10:37
"The current table uses the DATE datatype, inserted as 'DD-MMM-YYYY' but when selected it's displayed as 'DD-MMM-YY' (I don't know why.)" - That is just the way Oracle displays dates by default. If you need to change the way the date is displayed, use the `TO_CHAR()` function with a mask (e.g., `TO_CHAR(mydate, 'DD-MMM-YYYY')`). Or, better yet, modify the display in the display layer. — David Faber, Jan 03 '15 at 16:14

Mureinik · Answer 1 · 2015-01-03T07:42:22.750

1

Triggers aren't really meant for data validation. Why not use a check constraint instead?

ALTER TABLE sports_event
ADD CONSTRAINT not_in_december_ck
CHECK (TO_CHAR(event_date, 'MM') != '12')

edited Jan 03 '15 at 07:42

answered Jan 03 '15 at 07:07

Mureinik

297,002
52
306
350

Thanks for the reply. Is there no way to add a constraint but in a trigger format? The exercise that I'm trying to do this for specifically asks to create a trigger to ensure the event cannot happen in a certain month. Currently no event happens in December, but for future purposes, the trigger will prevent any from being created with an sports event date in Dec. – Tootally Jan 03 '15 at 07:34

score 1 · Accepted Answer · edited Jan 03 '15 at 13:09

I'm trying to create a simple trigger to ensure a sports event cannot happen in a certain month

[...]

The exercise that I'm trying to do this for specifically asks to create a trigger to ensure the event cannot happen in a certain month.

As this is for homework / educational purpose, here are some hints first:

First, as this was said by Mureink in his answer, remember that a CHECK CONSTRAINT is the preferred way to do data validation;
Then, as you are required to use a trigger, you will need both an INSERT trigger and an UPDATE trigger;
As you will do data validation, you need a BEFOREINSERT OR UPDATE trigger;
You will access to incoming data using the NEW. pseudo-record;
And you will reject DML statement by raising an exception.

You already have the (2) and (3) in your code. Starting from that, one complete solution might look like this:

CREATE OR REPLACE TRIGGER event_test
BEFORE INSERT OR UPDATE
ON sports_event
FOR EACH ROW WHEN (EXTRACT(MONTH FROM NEW.event_date) = 12)
BEGIN
    RAISE_APPLICATION_ERROR (
           num=> -20107,
           msg=> 'Sports cannot occur during December.');
END;

^{Untested. Beware of typos !}

Thank you, this was very helpful. I wasn't sure of the format when specifying the month, and am surprised that it uses the number format of the month instead of the abbreviated spelling. This has made me understand triggers even more. — Tootally, Jan 03 '15 at 17:28

Trigger to ensure a value cannot happen

2 Answers2