triggers to monitor stock options backdating

[whiskeylima64]

I'm a newbie in oracle 10g - I was hoping if some one could walk me through writing a trigger function for this particular problem:

the scenario:
" ... the practice is called backdating because an executive manages to move the date of a stock option back in time, presumably to when the stock price was lower. Stock options grant the recipient the right to buy shares at the stated price after a certain period of time has gone by. If the market price is higher than the so-called strike price, the employee makes money. Find a way to lower the price of the grant - by moving back the grant date during a rising market, for example - and the option is worth even more."

the policy has to be:
The exercise date cannot be more than 3 days either before or after the grant date. Write a database trigger that does not allow entering a new grant record or modifying an existing one that violates this policy.

So I've written:

create or replace trigger date_violation
before insert or update on option_grants
for each row
 
declare
 
   date_diff number;
 
begin
 
   date_diff := exercise_date - grant_date;
   if (abs(date_diff) >= 3)
 
then
 
raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before the grant date.');
 
end if;
 
end;
/
 

--but I'm getting this error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1      PL/SQL: Statement ignored
7/14     PLS-00201: identifier 'EXERCISE_DATE' must be declared
 

------------------------------------------

I'm not sure what to define.

Here's what my table looks like:

 
create table option_grants
(grant# varchar2(4),
grant_date date,
exercise_date date,
emp_id number(9),
stock_symbol char(3),
strike_price number(5,2));
 
insert into option_grants
values ('A234', '25-Jan-04', '23-Jan-04', 123456789, 'XYZ', '12.54');
insert into option_grants
values ('A345', '23-Mar-05', '23-Mar-05', 666555444, 'XYZ', '18.22');
insert into option_grants
values ('B456', '15-Apr-06', '15-Apr-06', 123456790, 'XYZ', '13.79');
insert into option_grants
values ('C956', '24-Dec-06', '23-Dec-06', 666555444, 'XYZ', '9.75');
 

I know that this is alot ... I really appreaciate the help.

Thank you and please have an outstanding day.

[whiskeylima64]

Hey - To whom it concerns... I figured out my issue... I'm an idiot

create or replace trigger date_violation
before insert or update on option_grants
for each row
 
declare
 
date_diff number;
 
begin
 
date_diff := :new.exercise_date - :old.grant_date;
 
if (abs(date_diff) >= 3)
 
then
 
raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before the grant date.');
 
end if;
 
end;
/
 

[sbldragon]

i like ur post. this example help me to improve my trigger :) i'm new in oracle