PL/SQL Triggers

Question

I am using these tables:

• flights (flno, origin, destination, distance, departs, arrives, price)
• aircraft (aid, aname, crusingrange)
• employees (eid, ename, salary)
• certified (eid,aid)

and I need to create a trigger that displays a warning when inserting an employee with "666" anywhere in his/her name.

This is what I came up with so far; I am lost with the rest of it.

set serveroutput on
create or replace trigger emp_warning
before insert
on employees
for each row
declare
 v_name;
begin
 select e.ename into v_ename
 from employees e

Answer 1

A trigger cannot "display a warning"; a trigger can raise an exception.

In the context of the body of a before insert for each row trigger, the value being supplied for the column is available from :NEW.columname

For example:

 BEGIN
    IF :NEW.ename LIKE '%666%' THEN
       RAISE_APPLICATION_ERROR(-20000, 'ename contains ''666''.');
    END IF;
 END;

It's not mandatory that you use the RAISE_APPLICATION_ERROR. You could emit some line(s) using DBMS_OUTPUT.PUT_LINE... the line could include whatever text you wanted, including the word "warning". But this isn't really a display of a warning.

Answer 2

Use a check constraint instead of a trigger:

alter table empoloyees modify ename check (ename not like '%666%');

Answer 3

I finally figured it out thanks for you help. This my answer to the trigger question.

set serveroutput on
create or replace trigger name_warning
before insert on employees
for each row
begin
if :new.ename like '%666%' then
dbms_output.put_line('Warning employees name contains 666');
end if;
end;
/