CODEWITHSUNDEEP
sqloracle-databasetriggers
Tootally
Tootally

Reputation: 47

Trigger to ensure a value cannot happen

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.

Upvotes: 1

Views: 85

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

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:

  1. First, as this was said by Mureink in his answer, remember that a CHECK CONSTRAINT is the preferred way to do data validation;
  2. Then, as you are required to use a trigger, you will need both an INSERT trigger and an UPDATE trigger;
  3. As you will do data validation, you need a BEFOREINSERT OR UPDATE trigger;
  4. You will access to incoming data using the NEW. pseudo-record;
  5. 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 !

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

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')

Upvotes: 1

Related Questions