Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (Oracle9i)
Triggers [message #438740] Tue, 12 January 2010 00:47 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Please give me one example for this scenario on Trigger

How can we implement primary key on a column using trigger.

The EMP table contains following data

Empno
7369
7367
7368
7385
7462

If any Duplicate value or Null value is inserted in this column the trigger will display the message
like "You cannot insert duplicate values or NULL values in this column."
Please help me thanks in advance.
I appriciate for your response.
Re: Triggers [message #438743 is a reply to message #438740] Tue, 12 January 2010 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should not use a trigger to validate constraints; a primary key will throw an error when violated.
This error can be caught by your (frontend)application and translated to something more readable.
Re: Triggers [message #438748 is a reply to message #438740] Tue, 12 January 2010 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can we implement primary key on a column using trigger.

It is almost impossible in a performant multi-user environment.

The way is to create a PRIMEY KEY constraint: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#g1053592

Regards
Michel
Re: Triggers [message #438758 is a reply to message #438743] Tue, 12 January 2010 01:44 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Frank wrote on Tue, 12 January 2010 07:57
You should not use a trigger to validate constraints; a primary key will throw an error when violated.
This error can be caught by your (frontend)application and translated to something more readable.
This is an excellent advise: let the calling environment deal with it. You could catch the predefined DUP_VAL_ON_INDEX exception.

MHE
Previous Topic: Error
Next Topic: upload a text file in oracle table
Goto Forum:
  


Current Time: Mon Sep 26 02:32:26 CDT 2016

Total time taken to generate the page: 0.04957 seconds