Home » SQL & PL/SQL » SQL & PL/SQL » New in PL/SQL Triggers
New in PL/SQL Triggers [message #353303] Mon, 13 October 2008 05:20 Go to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Hi,

I'm new in PL/SQL...

I'm trying to make one BEFORE INSERT trigger that don't alow you to add a new record if the first record of the field 'value' is not 0 (zero).

What principles should I apply to do this?

Sorry for my bad english.


Best Regards, André.
Re: New in PL/SQL Triggers [message #353307 is a reply to message #353303] Mon, 13 October 2008 05:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You don't need trigger for that.

Check constraint will do the job.

Regards,
Rajat Ratewal
Re: New in PL/SQL Triggers [message #353316 is a reply to message #353303] Mon, 13 October 2008 05:30 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Where can I see examples on how to do this? I'am using PL/SQL developer, I see where is the CHECK but I don't know how to add a CHECK to do that.

Best Regards, André.
Re: New in PL/SQL Triggers [message #353318 is a reply to message #353316] Mon, 13 October 2008 05:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
./fa/456/0/ Google Next Time.


CHECK CONSTRAINT


Regards,
Rajat
Re: New in PL/SQL Triggers [message #353326 is a reply to message #353316] Mon, 13 October 2008 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Welcom to the wonderful world of Documentation

You want a constraint like CHECK (substr(<fieldname>,1,1) != '0')
Re: New in PL/SQL Triggers [message #353328 is a reply to message #353303] Mon, 13 October 2008 05:48 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Hi, I have searched, but I can't find how to give the order for verify if the first record of value is 0 (zero), the other records could be other values greater than 0 (zero).

CONSTRAINT verify_value
CHECK (value = 0)

How can I change this to do me the job?


Best Regards, André.
Re: New in PL/SQL Triggers [message #353331 is a reply to message #353328] Mon, 13 October 2008 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To do this you have to make your inserts inside a procedure which locks the table.

Regards
Michel
Re: New in PL/SQL Triggers [message #353333 is a reply to message #353331] Mon, 13 October 2008 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could get the trigger to store whether or not it has processed a 'first record' using either Sys_Context or DBMS_APPLICATION_INFO
You couldn then condition the behaviour of the trigger on this information.
You'd need a statement level AFTER trigger to clear up after yuo and reset the values.
Re: New in PL/SQL Triggers [message #353335 is a reply to message #353303] Mon, 13 October 2008 06:15 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
I think the CHECK CONSTRAINT will not do the job.

If the first record to a Key is not 0 (zero) I don't want allow insert. ID and TYPE are PK's.

id | type | value
-----------------
1 | 1 | 2

If I try to insert this I need that the oracle do not alow the insert. The first value it is not 0 (zero)




If the sequence is this one, the oracle will alow insert:

id | type | value
-----------------
1 | 1 | 0
2 | 1 | 2
3 | 1 | 3
4 | 2 | 0
5 | 2 | 4
6 | 2 | 6


1 | 1 | 0, in the first record the value is 0 (zero)

4 | 2 | 0, in the first record the value is 0 (zero)


It is possible to do this only with CHECK CONSTRAINT?


Best Regards, André.

Re: New in PL/SQL Triggers [message #353337 is a reply to message #353335] Mon, 13 October 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it is not possible.
Michel Cadot wrote on Mon, 13 October 2008 12:53
To do this you have to make your inserts inside a procedure which locks the table.


Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: New in PL/SQL Triggers [message #353355 is a reply to message #353337] Mon, 13 October 2008 09:00 Go to previous messageGo to next message
hijack
Messages: 4
Registered: October 2008
Junior Member
FYI.
create or replace trigger tr_t3_value
before insert on t3
referencing new as newrow
for each row
when (newrow.value!=0)
declare
r_t3 VARCHAR2(10);
begin
  select 1 into r_t3 
  from t3 
  where type= :newrow.type 
        and value=0;
  exception
  when NO_DATA_FOUND then
  raise_application_error(-20055,'the value no matched 0 value');
end tr_t3_value; 

[Updated on: Mon, 13 October 2008 09:07]

Report message to a moderator

Re: New in PL/SQL Triggers [message #353358 is a reply to message #353355] Mon, 13 October 2008 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, there's a developer with no fear of mutating table errors.

Did you actually test that code?
Re: New in PL/SQL Triggers [message #353378 is a reply to message #353355] Mon, 13 October 2008 12:37 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
hijack,

As already said to you: don't post full solution.
And obviously don't post full wrong solution.

Regards
Michel
Previous Topic: Problem with pl/sql for loop
Next Topic: ORA-01031
Goto Forum:
  


Current Time: Sun Dec 11 02:43:08 CST 2016

Total time taken to generate the page: 0.09245 seconds