Home » SQL & PL/SQL » SQL & PL/SQL » create trigger problem (Oracle)
create trigger problem [message #438466] Sat, 09 January 2010 05:42 Go to next message
user978
Messages: 1
Registered: January 2010
Junior Member
Please I want your support to create trigger in oracle

I am trying to create secure_emp trigger as bellow:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||' into EMPLOYEES table only during ' ||' business hours.');
END IF;
END;


But I recieved the bellow error
"pls-00201 identifier 'raise_application_error' must be declared"
Re: create trigger problem [message #438467 is a reply to message #438466] Sat, 09 January 2010 05:52 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It compiles well on my Oracle 10 XE (10.2.0.1):
SQL> CREATE OR REPLACE TRIGGER secure_emp
  2     BEFORE INSERT
  3     ON employees
  4  BEGIN
  5     IF    (TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN'))
  6        OR (TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
  7     THEN
  8        raise_application_error (-20500,
  9                                    'You may insert'
 10                                 || ' into EMPLOYEES table only during '
 11                                 || ' business hours.'
 12                                );
 13     END IF;
 14  END;
 15  /

Trigger created.

SQL>


Please, post your Oracle database version and copy-paste your SQL*Plus session which will show what you do.

[Updated on: Sat, 09 January 2010 05:53]

Report message to a moderator

Re: create trigger problem [message #438489 is a reply to message #438466] Sat, 09 January 2010 16:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Your problem may just have been an errant byte in your cut/paste. If you cut/paste Littlefoot's version into your SQLPLUS session you should see it work.

Additionally, there are other ways to do what you want that do not require use of triggers. I like instead of triggers a lot, and I stay away for table triggers like the plague. This is just my way of doing things and others have differing opinions. However, I have learned that if you can do it without a trigger, then unless you have a reason to use triggers (most notably because you have a design strategy that goes across your entire database that exploits triggers in a specific consistent fashion and you do not want to break it), then in general the non-trigger solution will offer easier results.

Nornally I would ask you to try first but since you have what appears to be one working example, I feel fine posting this as an alternative solution. It involves default values and check constraints. Of course this solution has some flaws. You can bypass it if your application cheats by actually supplying a value that works. You could fix this by using either views to hide the column, or possibly implementing a fine grained access control policy on the column (not sure about this one but Tom Kyte suggested same in one of his posts so...).

However, this makes for an interesting point. I said usually a solution that does not require a trigger will be superior to a solution that does require a trigger because of the issues that come with table level triggers. But complexity of a solution should always be a concern and weighted heavily in any evaluation of that solution. Looking at the alternative I provide below one makes the following observation: it require use of several different oracle features:

1) use of a dummy column
2) use of default value
3) use of check constraint
4) use of views and/or auditing to hide the dummy column

Anyone reflecting on my alternative should consider that a trigger is pretty straight forward compared to my alternative. A trigger uses only one oracle feature that most oracle developers/dbas would be expected to know. My alternative requires the successful co-operation of multiple oracle features and design techniques to work.

With that in mind I would use the trigger based solution and not this alternative so in this particular thread, I only offer the alternative in order for us to discuss it and learn things.

My alternative is a "DECLARATIVE" solution that requires no true code behind it. The trigger solution is a "CODING" solution and all that this implies. Nine times out of Ten a declarative solution is the way to go but this is one of those 1 out of 10 times it is not. There are simply too many working parts in the alternative to make it worth while.

drop table edit_example
/

--
-- add a dummy date column to your table add give it a default value of sydate
-- then use a check constraint to check your logic against the dummy column
--
create table edit_example
(
   some_date date default sysdate not null
, constraint check_1 check (
                              TO_CHAR(some_date,'DY') NOT IN ('SAT','SUN') or
                              TO_CHAR(some_date,'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
                           )
)
/

insert into edit_example values (sysdate);
insert into edit_example values (sysdate+1);
insert into edit_example values (sysdate+2);
insert into edit_example values (sysdate+3);
insert into edit_example values (sysdate+4);
insert into edit_example values (sysdate+5);
insert into edit_example values (sysdate+6);
insert into edit_example values (sysdate+7);

alter session set nls_Date_format = 'DAY-MON-RRRR';

SQL> select * from edit_example;

SOME_DATE
------------------
MONDAY   -JAN-2010
TUESDAY  -JAN-2010
WEDNESDAY-JAN-2010
THURSDAY -JAN-2010
FRIDAY   -JAN-2010


Good luck, Kevin

[Updated on: Sat, 09 January 2010 16:33]

Report message to a moderator

Re: create trigger problem [message #438515 is a reply to message #438489] Sun, 10 January 2010 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin,

I don't think that getting "ORA-02290: check constraint violated" is the same than getting "ORA-20500: You may insert into EMPLOYEES table only during business hours".
The first one will lead to a call to the DBA: "Why I can't insert my data when I could do the same thing yesterday?" when the second one will just lead to the user to know he is breaking a rule (if he didn't know it) or he can't break it anyway (if he was deliberately breaking a policy).
Also the trigger allows to record the security violation.

Regards
Michel
Re: create trigger problem [message #438686 is a reply to message #438466] Mon, 11 January 2010 15:14 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Excellent points Michel, thanks.

Just goes to show why it is necessary to do a truthful evaluation of options before adopting one. To recap, although the solution I offered up for discussion is a declarative based solution and thus usually more favored that a solution that requires coding, this is clearly a case where a coded solution using a trigger etc. is much more desireable.

Thanks for you help. Kevin
Previous Topic: Constraint and Trigger
Next Topic: joining 3 tables - how Oracle decides about execution plan
Goto Forum:
  


Current Time: Wed Dec 07 04:38:33 CST 2016

Total time taken to generate the page: 0.08056 seconds