CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
devBem
devBem

Reputation: 840

Creating trigger which throws an exception on insert

I have few university tasks for winter break and one of them is to create trigger on table:

PERSON(ID, Name, Surname, Age);

Trigger is supposed to inform user when they have inserted row with invalid ID. Validity criteria is that ID is 11 digits long.

I tried to write solution like this:

CREATE OR REPLACE TRIGGER person_id_trigg
AFTER INSERT
ON person
DECLARE
  idNew VARCHAR(50);
  lengthException EXCEPTION;
BEGIN
  SELECT id INTO idNew FROM INSERTED;
  IF LENGTH(idNew) <> 11 THEN
  RAISE lengthException;
  END IF;
  EXCEPTION
  WHEN lengthException THEN
  dbms_output.put_line('ID for new person is INVALID. It must be 11 digits long!'); 
END;

Then I realized that INSERTED exists only in SQL Server and not in oracle.

What can I could do to fix that?

Upvotes: 3

Views: 40139

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Do you want to raise an exception (which would prevent the insert from succeeding)? Or do you want to allow the insert to succeed and write a string to the dbms_output buffer that may or may not exist and may or may not be shown to a human running the insert?

In either case, you'll want this to be a row-level trigger, not a statement-level trigger, so you'll need to add the for each row clause.

CREATE OR REPLACE TRIGGER person_id_trigg
  AFTER INSERT
  ON person
  FOR EACH ROW

If you want to raise an exception

BEGIN
  IF( length( :new.id ) <> 11 )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
                             'The new ID value must have a length of 11' );
  END IF;
END;

If you want to potentially print output but allow the insert to succeed

BEGIN
  IF( length( :new.id ) <> 11 )
  THEN
    dbms_output.put_line( 'The new ID value must have a length of 11' );
  END IF;
END;

Of course, in reality, you would never use a trigger for this sort of thing. In the real world, you would use a constraint.

Upvotes: 9

Related Questions