OraFAQ Forum: SQL & PL/SQL » trigger and function

Home » SQL & PL/SQL » SQL & PL/SQL » trigger and function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trigger and function [message #196819]

Sat, 07 October 2006 22:46

keegan
Messages: 2
Registered: October 2006
Location: Malaysia

Junior Member

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;

Report message to a moderator

Re: trigger and function [message #196848 is a reply to message #196819]

Sun, 08 October 2006 09:24

Littlefoot
Messages: 21823
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

Re: trigger and function [message #197446 is a reply to message #196848]

Wed, 11 October 2006 05:06

keegan
Messages: 2
Registered: October 2006
Location: Malaysia

Junior Member

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

Report message to a moderator

Re: trigger and function [message #197454 is a reply to message #197446]

Wed, 11 October 2006 05:25

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	Help with Date error
Next Topic:	display of sum 1 to 100

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 00:02:48 CST 2024