Home » SQL & PL/SQL » SQL & PL/SQL » Before Insert Trigger (Oracle express 10.2.0.1)
Before Insert Trigger [message #404005] Wed, 20 May 2009 00:58 Go to next message
kalyogi
Messages: 11
Registered: July 2007
Junior Member
Hi all,

I need to create a "Before Insert Trigger" for the following requirement:

Table:
CREATE TABLE DYNAMIC_TEXT 
   (DYNAMIC_TEXT_ID NUMBER(10,0), 
	SEQNO NUMBER(10,0), 
	STATICTEXT CHAR(1 BYTE) DEFAULT 'Y',
	TEXT VARCHAR2(50),
	NAME VARCHAR2(50), 
	NEWLINE CHAR(1 BYTE) DEFAULT 'N'	
  );

In the table there is a field called "STATICTEXT (YES/NO)". If STATICTEXT = 'Y', then TEXT field should be filled, else NAME field should be filled. But my requirement is either TEXT OR NAME fields cannot be filled second time until they select the NEWLINE = 'Y'.

Example:
SEQNO 10 , STATICTEXT = 'Y', TEXT = 'xyz';
SEQNO 20 , STATICTEXT = 'N', NAME = 'oracle';
SEQNO 30 , NEWLINE = 'Y';
SEQNO 40 , STATICTEXT = 'N', NAME = 'is';
SEQNO 50 , NEWLINE = 'Y';
SEQNO 60 , STATICTEXT = 'N', NAME = 'the world's largest enterprise software company';
SEQNO 70 , NEWLINE = 'Y';
SEQNO 80, STATICTEXT = 'Y', TEXT = 'abc';

the above 8 lines are 8 different records of the table.

User can fill TEXT or NAME fields without selecting NEWLINE = 'Y', that's why I would like to control before inserting.

I hope I have explained it clear. How can I write a trigger for this requirement?

Thanks in advance.

[Updated on: Wed, 20 May 2009 01:15] by Moderator

Report message to a moderator

Re: Before Insert Trigger [message #404010 is a reply to message #404005] Wed, 20 May 2009 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a whole process, so insert should be done through a procedure not from a direct INSERT statement from the client.
In addition, you have to lock the table during the process as this is a stand-alone table, it does not support multiuser.

Regards
Michel
Re: Before Insert Trigger [message #404013 is a reply to message #404010] Wed, 20 May 2009 01:25 Go to previous messageGo to next message
kalyogi
Messages: 11
Registered: July 2007
Junior Member
Hi Michel Cadot,

Thanks for your reply.

The client will insert into this table from the front end, which means it should be a normal insert (please correct me if I am wrong).

And only one user has the permission to access this window(table).


Thanks.
Re: Before Insert Trigger [message #404030 is a reply to message #404013] Wed, 20 May 2009 02:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which means it should be a normal insert

It might be but you can also call a procedure and you should here and in any case where there is a complex algorithm associated to the operation.

Quote:
And only one user has the permission to access this window

One use does not mean one session or transaction. Locking is mandatory and does not rely in implicit assumption like the fact there is only one connection on the database, for instance because sooner or later implicit assumption comes wrong.

Regards
Michel
Previous Topic: procedure executed successfully but no data stored in table (merged)
Next Topic: Hierarchial queries with union.
Goto Forum:
  


Current Time: Sun Dec 04 18:39:42 CST 2016

Total time taken to generate the page: 0.11515 seconds