Home » SQL & PL/SQL » SQL & PL/SQL » problem with table trigger?
problem with table trigger? [message #313716] Mon, 14 April 2008 05:13 Go to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've create a trigger on a table and it's job is to pervent user from insert or update if there is an equal value.
I've create a function that return certain value if it founds value.

the table:

CREATE TABLE IND_VALUE
(
  IND_VALUE_ID NUMBER (10,0) NOT NULL,
  IND_CLASS_ID NUMBER (5,0),
  IND_ID NUMBER (10,0),
  UNIT_ID NUMBER (5,0),
  SOURCE_ID NUMBER (5,0),
  IND_VALUE NUMBER,
  IND_TIME_LEVEL_VALUE CHAR (1),
  IND_TIME_YEAR CHAR (4),
  IND_TIME_MONTH CHAR (1),
  IND_GEO_LEVEL_VALUE CHAR (1),
  CON_ID NUMBER (5,0)
)


The function:
CREATE OR REPLACE FUNCTION get_ind_value ( p_whr varchar2)
RETURN  number
 IS
   type ind_refcur_type is ref cursor;
    ind_refcur ind_refcur_type;
    v_count_val number;
 BEGIN
   open ind_refcur for
   'select count(IND_VALUE_ID) from ind_value
   where '||p_whr;
   LOOP
     FETCH ind_refcur  INTO  v_count_val;
     EXIT WHEN ind_refcur%NOTFOUND;
   END LOOP;
   if v_count_val>0 then
    RETURN (1);
   else
    return (0);
   end if;
END;


and the trigger:
CREATE OR REPLACE TRIGGER CHECK_IND_VALUE
BEFORE  INSERT OR UPDATE OF IND_CLASS_ID, IND_ID, UNIT_ID, SOURCE_ID,
 IND_TIME_LEVEL_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, IND_GEO_LEVEL_VALUE, CON_ID
ON IND_VALUE
REFERENCING OLD AS OLD NEW AS NEW
for each row
declare
V_COUNT_VALUE number;
BEGIN
 if inserting then
    V_COUNT_VALUE:=RHH.get_ind_value
    ( 'IND_CLASS_ID='||:NEW.IND_CLASS_ID||
    ' and IND_ID='||:NEW.IND_ID||
    ' and UNIT_ID='||:NEW.UNIT_ID||
    ' and SOURCE_ID='||:NEW.SOURCE_ID||
    ' and IND_TIME_LEVEL_VALUE='||:NEW.IND_TIME_LEVEL_VALUE||
    ' and IND_TIME_YEAR='||:NEW.IND_TIME_YEAR||
    ' and IND_TIME_MONTH='||:NEW.IND_TIME_MONTH||
    ' and IND_GEO_LEVEL_VALUE='||:NEW.IND_GEO_LEVEL_VALUE||
    ' and CON_ID='||:NEW.CON_ID );
   IF V_COUNT_VALUE > 0 THEN
RAISE_APPLICATION_ERROR(-20003,'THERE IS ANOTHER ROW');
   END IF;
 END IF;
END;


I've oracle 10g relase2.
Regards,
Re: problem with table trigger? [message #313719 is a reply to message #313716] Mon, 14 April 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is the question?
Where is the problem?
Where is the error?
Where is the session execution?

Regards
Michel
Re: problem with table trigger? [message #313722 is a reply to message #313719] Mon, 14 April 2008 05:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also : Why not simply use a unique constraint over the columns?
Re: problem with table trigger? [message #313732 is a reply to message #313722] Mon, 14 April 2008 06:28 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thank's for your reply.
The problem is that when i'm trying to insert into the table it give me this error:
ORA-00936 missing expression
ORA-06512: at get_ind_value line 8
ORA-06512: at CHECK_IND_VALUE line 5
ORA-04088: error during execution of trigger CHECK_IND_VALUE

Regards,
Re: problem with table trigger? [message #313738 is a reply to message #313732] Mon, 14 April 2008 06:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then there is an error in the dynamic SQL.

Possibly missing single quotes around the char values, I guess.

One of the rules you should follow when using dynamic SQL :

Avoid it whenever possible, and when there is absolutely definitely no other way to do it, at least include the SQL String in the error message when something goes wrong. Otherwise debugging becomes almost impossible.

Once you have fixed the SQL you will most likely run into an mutating table error next, by the way. So why not simply use a unique constraint over the columns?
Re: problem with table trigger? [message #315245 is a reply to message #313738] Sun, 20 April 2008 06:28 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've tried to use pragma Autonomous Transactions and it solved my problem, but I'd like to know if there are any problem from using it?
Regards
Re: problem with table trigger? [message #315246 is a reply to message #315245] Sun, 20 April 2008 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'd like to know if there are any problem from using it?

Of course there are.
If Oracle does not allow to query/modify the table that is currently modified there is a good reason: transaction.
Using autonomous transaction, you force to break transaction rules started in another transaction, and of course you are no more protected against wrong results.
Think about it. You are no more in the initial transaction, so you can't see the modifications made by this one, so the query you make in other transaction does not and can't validate what you did in the original one.
Too bad as this is the purpose of your function.

Check it is easy, you can insert twice the row you don't want.

By the way, what you try to do is this not a unique constraint?

In addition, search for "SQL injection"; give me access to your function and your data are in danger.

Regards
Michel


Regards
Michel

[Updated on: Sun, 20 April 2008 06:41]

Report message to a moderator

Re: problem with table trigger? [message #315300 is a reply to message #315246] Mon, 21 April 2008 00:48 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thank's for your reply, but can i make a unique constraint over 21 columns?
I've think to use a pl/sql table and make all changes on its data including the select to avoid this problem, but I'd like to ask you is it better to use pl/sql table or database table?

Regards.
Re: problem with table trigger? [message #315304 is a reply to message #315300] Mon, 21 April 2008 00:56 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
database triggers are safe unless you have any other
specific reason with repect to porting to data..

yours
dr.s.raghunathan
Re: problem with table trigger? [message #315305 is a reply to message #315300] Mon, 21 April 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can i make a unique constraint over 21 columns?

Yes if the total length of your columns does not exceed maximum size for a key (which depends on block size).

Quote:
I've think to use a pl/sql table and make all changes on its data including the select to avoid this problem

This will never guarantee the unicity.
Imagine 2 transactions inserting the same rows at the same time, they don't see the rows the other one insert so they each think their rows are unique.

Regards
Michel
Re: problem with table trigger? [message #315321 is a reply to message #315305] Mon, 21 April 2008 01:30 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks for your reply, but i didn't understand how it depends on block size.

Regards,
Re: problem with table trigger? [message #315322 is a reply to message #315321] Mon, 21 April 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The limit depends on block size, key size must be less than about 70% of it.

Regards
Michel
Re: problem with table trigger? [message #315324 is a reply to message #315322] Mon, 21 April 2008 01:42 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks,
They're exactly 70% of block size, do you think it's work?

Regards,
Re: problem with table trigger? [message #315344 is a reply to message #315324] Mon, 21 April 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it, if you can't you will get an error:
SQL> alter table t add (c1 char(2000), c2 char(2000), c3 char(2000), c4 char(1000));

Table altered.

SQL> alter table t add unique (c1,C2,C3,c4);
alter table t add unique (c1,C2,C3,c4)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

Regards
Michel
Re: problem with table trigger? [message #315358 is a reply to message #315344] Mon, 21 April 2008 05:08 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks for your help it works, but if i want to success the trigger can i do it with befor statment instead of for each raw.

Regards,
Re: problem with table trigger? [message #315361 is a reply to message #315358] Mon, 21 April 2008 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it successfully with a trigger, any way you built it.
Or rather you can do it with a trigger if you lock the table in exclusive mode.

Regards
Michel

[Updated on: Mon, 21 April 2008 05:13]

Report message to a moderator

Re: problem with table trigger? [message #315367 is a reply to message #315361] Mon, 21 April 2008 05:26 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
i couldn't understand how to do it in an exclusive mode.

Regards,
Re: problem with table trigger? [message #315371 is a reply to message #315367] Mon, 21 April 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I will not spend time to search how to do it, if ever it is possible.
Trigger is a wrong way.
Either use unique constraint, either use procedure to insert your rows. Don't use trigger.

Regards
Michel
Re: problem with table trigger? [message #315373 is a reply to message #315371] Mon, 21 April 2008 05:42 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Ok i got it.
Could you led me how to do it with procedure.

Regards
Re: problem with table trigger? [message #315376 is a reply to message #315373] Mon, 21 April 2008 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do in the procedure (application or PL/SQL one) what you wanted to do in the trigger.

For example:

begin
lock table
insert
check here or only at the end, if wrong raise my_error
insert
check here or only at the end, if wrong raise my_error
insert
check here or only at the end, if wrong raise my_error
insert
check here or only at the end, if wrong raise my_error
insert
check if wrong raise my_error
commit
exception when my_error then rollback
end

Just an example.

Regards
Michel

Re: problem with table trigger? [message #315383 is a reply to message #315376] Mon, 21 April 2008 06:16 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
And when i run it? is it at form or when?

Regards,
Re: problem with table trigger? [message #315398 is a reply to message #315383] Mon, 21 April 2008 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know this is your application.
You know what is it, the language, the environement, the logic, etc. I don't know them.

Regards
Michel
Re: problem with table trigger? [message #315596 is a reply to message #315398] Tue, 22 April 2008 01:58 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks for your help.
My problem is that i've a lot of data which i frequently should import it into the database in addition to the data which will insert into the table through form (I've oracle developer 6i),
so I need to run this procedure in the database side.
any idea?

Regards,
Re: problem with table trigger? [message #315600 is a reply to message #315596] Tue, 22 April 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you can't execute this when importing, the ONLY sure way is the unique constraint.
Or you stop the concurrent transaction while you import and execute a batch to fix after that. Quite awful.

Regards
Michel
Re: problem with table trigger? [message #315602 is a reply to message #315600] Tue, 22 April 2008 02:27 Go to previous message
nadia74
Messages: 85
Registered: August 2007
Member
Thank's for your help.

Regards,
Previous Topic: How to use alias name for a column in sql query
Next Topic: If any Provision to Know the Updated records data.
Goto Forum:
  


Current Time: Sun Dec 11 08:03:47 CST 2016

Total time taken to generate the page: 0.14493 seconds