INSERT TRIGGER IN ORACLE

Question

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;

Would a CHECK constraint work better? Triggers are usually not a good idea for data validation. — Mike Christensen
– Mike Christensen, Commented Aug 18, 2012 at 1:39

Justin Cave · Accepted Answer · 2012-08-18 01:59:43Z

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'

Nir Alfasi · Accepted Answer · 2012-08-18 02:03:33Z

0

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.

edited Aug 18, 2012 at 2:03

answered Aug 18, 2012 at 1:41

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

1 Comment

Justin Cave Over a year ago

to_char(event_date, 'mm') will return '08'. So if you want to compare it to a string, you'd need '08' not '8'.

Collectives™ on Stack Overflow

INSERT TRIGGER IN ORACLE

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related