CODEWITHSUNDEEP
sqloracle-databasetriggersplsqloracle10g
Ibukun Gabriel Oladipupo
Ibukun Gabriel Oladipupo

Reputation: 21

INSERT TRIGGER IN ORACLE

I am new to triggers in Oracle. I created an EVENT table with this syntax:

CREATE TABLE Event
(event_id NUMBER (3) NOT NULL,
 event_date DATE NOT NULL,
 venue_id NUMBER (2) NOT NULL,
 concert_id NUMBER (3) NOT NULL
);

I want to create a trigger to ensure that concerts cannot run during the month of August. I tried the following code to create the trigger. The trigger was created successfully but after inserting a date with the month of August, it was inserted. This is not suppose to be. 

CREATE OR REPLACE TRIGGER check_date
  BEFORE INSERT ON event
DECLARE 
  event_date date;
BEGIN
  IF (to_char(event_date, 'mm') = 8) THEN
    raise_application_error(-20000, 'Concerts cannot be run during August');
  END IF;
END;

Upvotes: 2

Views: 1392

Answers (2)

Nir Alfasi
Nir Alfasi

Reputation: 53525

change:

IF (to_char(event_date, 'mm') = 8) THEN

to:

IF (to_char(event_date, 'mm') = '08') THEN

You're comparing between string and number.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

First, the trigger needs to be a row-level trigger not a statement-level trigger. You want the trigger to be fired for every row that is inserted not just once for every statement. Declaring the trigger a row-level trigger allows you to see the data for each row that is being inserted.

Second, you don't want to declare a local variable event_date. You want to look at :new.event_date which is the event_date for the row that is being inserted.

If I put those two together

CREATE OR REPLACE TRIGGER check_date
  BEFORE INSERT ON event
  FOR EACH ROW
BEGIN
  IF (to_char(:new.event_date, 'mm') = 8) THEN
    raise_application_error(-20000, 'Concerts cannot be run during August');
  END IF;
END;

then you'll get the behavior you want

SQL> insert into event values( 1, date '2012-08-01', 1, 1 );
insert into event values( 1, date '2012-08-01', 1, 1 )
            *
ERROR at line 1:
ORA-20000: Concerts cannot be run during August
ORA-06512: at "SCOTT.CHECK_DATE", line 3
ORA-04088: error during execution of trigger 'SCOTT.CHECK_DATE'

As a general matter of cleanliness, you also want to compare strings with strings and numbers with numbers. So you would want either

to_number( to_char(:new.event_date, 'mm') ) = 8

or

to_char(:new.event_date, 'fmmm') = '8'

or

to_char(:new.event_date, 'mm') = '08'

Upvotes: 5

Related Questions