Creating trigger which throws an exception on insert

Question

Hello fellow programmers and happy new year to you all!

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. Vadility 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 sqlserver and not in oracle.

What would you suggest I could do to fix that?

Thanks in advance!

Solution

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.