New in PL/SQL Triggers [message #353303] |
Mon, 13 October 2008 05:20  |
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 #353328 is a reply to message #353303] |
Mon, 13 October 2008 05:48   |
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 #353333 is a reply to message #353331] |
Mon, 13 October 2008 06:04   |
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   |
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 #353355 is a reply to message #353337] |
Mon, 13 October 2008 09:00   |
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 #353378 is a reply to message #353355] |
Mon, 13 October 2008 12:37  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|