Home » SQL & PL/SQL » SQL & PL/SQL » Simple Trigger Help Needed
Simple Trigger Help Needed [message #237186] Sun, 13 May 2007 03:01 Go to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Hey all - I'm a little new to Oracle and working out some commands here:

I have to write up a trigger policy that prohibits a certain date to be updated or inserted 3 days before or after another date.

so this is what i have... but it creates with compilation errors:

SQL> -- trigger that prohibits exercise_date to be set 3 days before or after grant_date
SQL>
SQL> create or replace trigger date_violation
2 before insert or update of exercise_date on option_grants
3 for each row
4
5 declare
6
7 date_diff number;
8
9 begin
10
11 date_diff := (exercise_date - grant_date);
12
13 if (date_diff >= 4)
14
15 then
16
17 raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before th
e grant date.')
18
19 end if;
20
21 end;
22 /

Warning: Trigger created with compilation errors.

Any help will be greatly appreciated.

Please have an outstanding day.
Re: Simple Trigger Help Needed [message #237187 is a reply to message #237186] Sun, 13 May 2007 03:22 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
First and foremost, please give your oracle version. On the same note, you have to format your code, give creation scripts for your tables as well.
Quote:
date_diff := (exercise_date - grant_date);

The values of exercise_date and grant_date have to be set up first either through a cursor or in the main trigger body. Otherwise the trigger will never compile.
Re: Simple Trigger Help Needed [message #237188 is a reply to message #237186] Sun, 13 May 2007 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format note is at: How to format your posts

In addition, "show errors" just after compilation or "show errors trigger date_violation" gives you the error listing. It is very helpful to post it.

Regards
Michel
Re: Simple Trigger Help Needed [message #237189 is a reply to message #237187] Sun, 13 May 2007 03:55 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Oh wow - thank you for the prompt reply:

saibel: I'm working with Oracle 10g. About the values for exercise_date and grant_date, a table was made for them. So the trigger is suppose to pull information from table option_grants:

Here's the created table for option_grant and values in them:

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');

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

and about the creation scripts... something like:

declare
temp_date option_grants.exercise_date

----not sure---
Re: Simple Trigger Help Needed [message #237190 is a reply to message #237188] Sun, 13 May 2007 03:56 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Thanks for the quick reply Michel:

thanks for the tip on the error msg:

SQL> show errors trigger date_violation
Errors for TRIGGER DATE_VIOLATION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
:= . ( % ;
The symbol ";" was substituted for "END" to continue.
Re: Simple Trigger Help Needed [message #237192 is a reply to message #237189] Sun, 13 May 2007 04:11 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Does this serve your purpose?
system@DBASE-SQL>> create or replace trigger t1 before update or insert on option_grants
2 declare
3 cursor c1 is select grant_date, exercise_date from option_grants;
4 crec c1%rowtype;
5 begin
6 for crec in c1 loop
7 if (crec.grant_date-crec.exercise_date)<=abs(3)
8 then raise_application_error(-20002,'Difference between grant dae and exercise date
9 must be more than three days');
10 end if;
11 end loop;
12 end;
13 /

Trigger created.

system@DBASE-SQL>>

[Updated on: Sun, 13 May 2007 04:33]

Report message to a moderator

Re: Simple Trigger Help Needed [message #237193 is a reply to message #237190] Sun, 13 May 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@whiskeylima64

Why didn't you read the link I posted or why didn't you want to apply it?

If you want help the least you can do is to show us a little respect and post what we are asking you.

Btw, version means 4 decimals version.

Regards
Michel
Re: Simple Trigger Help Needed [message #237194 is a reply to message #237192] Sun, 13 May 2007 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@saibal,

I bet if he uses your trigger he'll get a mutating error.

Regards
Michel
Re: Simple Trigger Help Needed [message #237196 is a reply to message #237194] Sun, 13 May 2007 04:35 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Thanx for pointing it out.....its a table level trigger now. Sorry for the gaffe
Re: Simple Trigger Help Needed [message #237200 is a reply to message #237193] Sun, 13 May 2007 04:47 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
I did get a mutating error:

update option_grants
*
ERROR at line 1:
ORA-04091: table DBUSER.OPTION_GRANTS is mutating, trigger/function may not see it
ORA-06512: at "DBUSER.T1", line 2
ORA-06512: at "DBUSER.T1", line 7
ORA-04088: error during execution of trigger 'DBUSER.T1'

Sorry Michel, version is 10.1.0.2.0 -- didn't think that was relevant.

I got the same error after changing that pls-00103 error.
Re: Simple Trigger Help Needed [message #237201 is a reply to message #237200] Sun, 13 May 2007 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You still don't read How to format your posts or don't want to apply it.
You'll not get any help from me.


Regards
Michel

Re: Simple Trigger Help Needed [message #237207 is a reply to message #237201] Sun, 13 May 2007 05:14 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Ah I see I see said the blind man...

sorry about that... so I have to create a trigger where the exercise_date cannot be more than or less than 3 days of the grant_date. Here's the complete:

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');

Here's the trigger I tried to write:
-- trigger that prohibits exercise date to be set 3 days before or after grant date

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 - grant_date;
if (date_diff >= 4)
then
raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before the grant date.')
end if;
end;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
:= . ( % ;
The symbol ";" was substituted for "END" to continue.
Re: Simple Trigger Help Needed [message #237208 is a reply to message #237207] Sun, 13 May 2007 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter table option_grants add constraints chk_date 
  2  check (abs(grant_date-exercise_date) <= 3) 
  3  /

Table altered.

No need of a trigger.

Regards
Michel
Re: Simple Trigger Help Needed [message #237214 is a reply to message #237186] Sun, 13 May 2007 07:52 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. You missed ';' at the end of
raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before the grant date.')

line of your trigger code.

2. You have to use if (ABS(date_diff) >= 4) as well.

However Michel's solution using CHECK constraints is the best one.

HTH.
Michael
Re: Simple Trigger Help Needed [message #237240 is a reply to message #237186] Sun, 13 May 2007 12:14 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Thanks guys - I really appreciate all the help.

Michel: yea, the constraint would be great - but it's a school assignment that explores triggers.

Micheal: Thanks for catching those.

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

Now, I have a new error -- so my question now is --> why do I have to define a grant_date identifier. I thought it would pick it up from the option_grants table? -- how would I declare it?:

Errors for TRIGGER DATE_VIOLATION:

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

----from----

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 - 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;
/

Re: Simple Trigger Help Needed [message #237241 is a reply to message #237240] Sun, 13 May 2007 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Database Application Developer's Guide - Fundamentals", chapter 9 "Coding Triggers".

Just a question for you: is there any difference between the use of "exercise_date" and "grant_date"?

Regards
Michel
Re: Simple Trigger Help Needed [message #237244 is a reply to message #237241] Sun, 13 May 2007 12:38 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
grant_date should not be modified, but I don't have to make that constraint.

I'm not quite sure what you mean by the use of the dates: all it is are just two separate dates which cannot be more than 3 days a part (pretty much).
Re: Simple Trigger Help Needed [message #237245 is a reply to message #237244] Sun, 13 May 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just talk about syntax (don't you have a syntax error?).
Theoricaly is there any difference?
In you code is there any difference?
Why?
Fix.

But read the chapter I posted. Not just try and try.
If you first read the chapter, you already have this answer instead of waiting for us to give you the solution since 10 hours.
It take only 1 hour to read the page, understand it and knowing all you have to now for a basic and quite advanced use of triggers.

Regards
Michel
Re: Simple Trigger Help Needed [message #237246 is a reply to message #237245] Sun, 13 May 2007 13:07 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
no there shouldn't be a difference.

could you link me to the chapter - I thought I knew how to create a trigger and what I wrote up was right.
Re: Simple Trigger Help Needed [message #237248 is a reply to message #237186] Sun, 13 May 2007 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com has many fine coding examples
Re: Simple Trigger Help Needed [message #237250 is a reply to message #237246] Sun, 13 May 2007 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no there shouldn't be a difference

So why there is in your code?
Quote:
could you link me to the chapter

Just click on "Coding Triggers" in one of my previous post.

Regards
Michel
Re: Simple Trigger Help Needed [message #237251 is a reply to message #237250] Sun, 13 May 2007 13:56 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Oh! I'm sorry - yea there is a difference -- sorry

the grant_date is a different date than the exercise_date.

the grant_date is when the grant is dated, and the exercise_date is the date that it is executed, which cannot be more than 3 days before or after the grant_date.

Thanks for the "coding triggers" I'll look into that
Re: Simple Trigger Help Needed [message #237252 is a reply to message #237251] Sun, 13 May 2007 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, I didn't talk about the meaning of the column but the syntax you used in your trigger.
Just look at them. Just look at what you wrote.

Regards
Michel
Re: Simple Trigger Help Needed [message #237255 is a reply to message #237252] Sun, 13 May 2007 18:03 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
yea... I don't know what's wrong... My ignorant-ass can't see anything wrong with the syntax... I'll hit up the professor.

Thanx for all your help.
Re: Simple Trigger Help Needed [message #237259 is a reply to message #237255] Sun, 13 May 2007 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
:new.exercise_date

vs
Quote:
grant_date

Regards
Michel
Re: Simple Trigger Help Needed [message #237436 is a reply to message #237259] Mon, 14 May 2007 08:42 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Michel Cadot wrote on Sun, 13 May 2007 23:45
Quote:
:new.exercise_date

vs
Quote:
grant_date

Regards
Michel


Did you get michel's hint???? What is before exercise_date that is not before grant_date?
Re: Simple Trigger Help Needed [message #237518 is a reply to message #237186] Mon, 14 May 2007 13:57 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Hey guys - thank you all for the help - I finally saw what I was missing in the syntax:

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;
/ 
Re: Simple Trigger Help Needed [message #237519 is a reply to message #237518] Mon, 14 May 2007 14:01 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
whiskeylima64 wrote on Mon, 14 May 2007 13:57
Hey guys - thank you all for the help - I finally saw what I was missing in the syntax:

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;
/ 


No.... Why are you comparing the difference between the exercise date after the update and the grant date from before the update. What happens if both the grant_date and exercise_date are both updated. Also you can't have an old value when you are inserting.
Re: Simple Trigger Help Needed [message #237533 is a reply to message #237519] Mon, 14 May 2007 15:21 Go to previous messageGo to next message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
Hey Bill - sorry, I neglected to mention that this is only the first part of the policy -- there's another policy that prohibits the change of the grant_date and there's also an audit trail for the attempted changes for both the exercise_date and grant_date.

Although good catch on the inserting, I didn't test out that function yet.

Regardless it was a school assignment that explored triggers, and I think this is good enough.

I really appreciate the help man... thank you and have an outstanding day.
Re: Simple Trigger Help Needed [message #237535 is a reply to message #237186] Mon, 14 May 2007 15:58 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
if you want a nice little trick to stop anyone from changing the grant_date then do the following


create or replace trigger date_violation
before insert or update on option_grants
for each row

begin

if updating then
  :new.grant_date := :old.grant_date;
end if;

if (abs(:new.exercise_date - :new.grant_date) >= 3)

then
  raise_application_error(-20003, 'Check date. Date cannot be more than 3 days after or before the grant date.');
end if;

end;
/ 
Re: Simple Trigger Help Needed [message #237624 is a reply to message #237535] Tue, 15 May 2007 04:34 Go to previous message
whiskeylima64
Messages: 13
Registered: May 2007
Junior Member
HA hey Bill,

Yea - that would have been smarter than creating another trigger policy.

Thanks man.
Previous Topic: like clause
Next Topic: How to use a boolean returned function in a query
Goto Forum:
  


Current Time: Sat Dec 03 13:49:30 CST 2016

Total time taken to generate the page: 0.08631 seconds