Home » SQL & PL/SQL » SQL & PL/SQL » trigger and function
icon8.gif  trigger and function [message #196819] Sat, 07 October 2006 22:46 Go to next message
keegan
Messages: 2
Registered: October 2006
Location: Malaysia
Junior Member

Embarassed here i want to modify this trigger to have separate function statement. please guide me!

create or replace trigger change_allowed_trig
after insert or update or delete
on student
begin
if to_char(sysdate, 'HH24') > '17' and to_char(sysdate, 'HH24') < '9' then
raise_application_error(-20100, 'You are not allowed to modify this table');
end if;
if to_char(sysdate, 'Day') = 'Saturday' and to_char(sysdate, 'Day') = 'Sunday' then
raise_application_error(-20200, 'You are not allowed to modify this table');
end if;
end;
Re: trigger and function [message #196848 is a reply to message #196819] Sun, 08 October 2006 09:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does it mean, "separate function statement"? I must admit that I don't understand it ...

However, a suggestion: using 'Day' isn't the best idea - it depends on your NLS settings. Your trigger, for example, doesn't fire in Croatia - we don't have 'Sunday' here - it is 'Nedjelja'. Therefore, perhaps you should use a day number instead of day name: TO_CHAR(SYSDATE, 'D'), where 6 and 7 are weekend days.

[EDIT]

I forgot to mention: this will never fire on weekend - SYSDATE can never be Saturday AND Sunday - you, actually, need OR, not AND.

[Updated on: Sun, 08 October 2006 09:25]

Report message to a moderator

icon5.gif  Re: trigger and function [message #197446 is a reply to message #196848] Wed, 11 October 2006 05:06 Go to previous messageGo to next message
keegan
Messages: 2
Registered: October 2006
Location: Malaysia
Junior Member

Embarassed
i wanna divide this statement to trigger and function which will be run in one piece of execution.(combine function in a trigger)...

plz guide me further...thanks
Re: trigger and function [message #197454 is a reply to message #197446] Wed, 11 October 2006 05:25 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So you'd like to have a function AND a trigger? What would you want trigger to do, and what should be function's task?

As I see it, this trigger is enough - a function would be unnecessary. Therefore, tweak the trigger code a little bit (not much; major guidelines from my point of view are in my last post) and it should be just fine.
Previous Topic: Help with Date error
Next Topic: display of sum 1 to 100
Goto Forum:
  


Current Time: Fri Dec 09 00:18:33 CST 2016

Total time taken to generate the page: 0.09833 seconds