Home » SQL & PL/SQL » SQL & PL/SQL » Trouble with trigger (Oracle)
Trouble with trigger [message #389209] Fri, 27 February 2009 11:17 Go to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Hi,

I'm trying to create a trigger to not allow update of a specified auto-inc key (only specified as auto-inc inside the application, not in the actual Oracle DB) - can anyone help debug the following? :

test
create trigger pppp_update
before update on pppp
for each row
DECLARE
invalid_update EXCEPTION;
begin
IF :new.pppp001 <> :old.pppp001
THEN RAISE invalid_update;
end if;
EXCEPTION
WHEN invalid_update THEN
raise_application_error(-20004,'Cannot modify auto-increment field')
end;


Thanks
Re: Trouble with trigger [message #389237 is a reply to message #389209] Fri, 27 February 2009 14:27 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What kind of debugging are you looking for (except the fact that RAISE_APPLICATION_ERROR requires semi-colon (;) at its end)?
Re: Trouble with trigger [message #389238 is a reply to message #389209] Fri, 27 February 2009 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Trouble with trigger [message #389240 is a reply to message #389209] Fri, 27 February 2009 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

If you want us to understand your problem you have to use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Trouble with trigger [message #389249 is a reply to message #389240] Fri, 27 February 2009 15:06 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Okay sorry - I'm not actually sure what version of Oracle it is, but I think it is a simple trigger so should not matter too much on the version. I am new to triggers though, and the error isn't very helpful.

The error I get is:

"ORA-04098: trigger [trigger name] is invalid and failed re-validation"

The table is called "PPPP" and the field is "PPPP001".

The formatted code is:

CREATE TRIGGER pppp_update 
  BEFORE UPDATE ON pppp 
  FOR EACH ROW 
DECLARE 
  invalid_update  EXCEPTION; 
BEGIN 
  IF :new.pppp001 <> :old.pppp001 THEN 
    RAISE invalid_update; 
  END IF; 
EXCEPTION 
  WHEN invalid_update THEN 
    Raise_application_error(-20004,'Cannot modify auto-increment field')
END; 

I relalise now I am missing a semi-colon after my error message. Does the rest look okay? I can't retry this until Monday when I'm back at work. Cheers.
Re: Trouble with trigger [message #389250 is a reply to message #389249] Fri, 27 February 2009 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, once you add the semi-colon, trigger will do the job.
Re: Trouble with trigger [message #389321 is a reply to message #389209] Sat, 28 February 2009 09:45 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Cool, cheers.
Re: Trouble with trigger [message #389468 is a reply to message #389249] Mon, 02 March 2009 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could write it in a simpler fashion by removing the exception handling:
CREATE OR REPLACE TRIGGER pppp_update 
  BEFORE UPDATE ON pppp 
  FOR EACH ROW 
BEGIN 
  IF :new.pppp001 <> :old.pppp001 THEN 
    Raise_application_error(-20004,'Cannot modify auto-increment field');
  END IF; 
END;
Re: Trouble with trigger [message #389469 is a reply to message #389468] Mon, 02 March 2009 04:04 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Cool, thanks. Both now work, I'll use the shorter version.
Re: Trouble with trigger [message #389470 is a reply to message #389249] Mon, 02 March 2009 04:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
sam_jk wrote on Fri, 27 February 2009 22:06
The error I get is:

"ORA-04098: trigger [trigger name] is invalid and failed re-validation"


And whenever you get that error, you can check with

SELECT * FROM user_errors;


(or with "sho err" in SQL*Plus directly after you get the "compiled with errors" message after compiling the trigger)

what the actual error in the actual code is.
Re: Trouble with trigger [message #389545 is a reply to message #389470] Mon, 02 March 2009 10:53 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Hi guys,

I'm having a bit more trouble.

The current code we use inserts a blank record then updates it for Oracle - so the auto-inc value is in memory. The whole point of the above code is to get round using extra SQL (i.e. we want to use 1 statement for Oracle instead of 2, the same as we do for MSSQL). And for MSSQL we use @@identity to get the last auto-inc.

After the insert in our java code base, we need to value back in memory and I'm having trouble getting the value. Note that we have 75 tables with auto-inc, and all have their own sequence. Bearing this in mind, how would I best get the latest auto-inc value straight back? I've been messing round with "returning" but not getting much to happen.

Our java calls the SQL using:

 result = con.writeStatement.executeUpdate(SQL);


I can't use autoGeneratedKeys, as it is in a later version. One example of what I've tried is (not formatted as is not syntactically correct) :
declare 
id DFPR.DFPR001%TYPE; 
begin 
select DFPR_seq.nextval into :new.DFPR001 from dual; 
returning DFPR001 into id; 
dbms_output.put_line(id);
end;

[Updated on: Mon, 02 March 2009 10:58]

Report message to a moderator

Re: Trouble with trigger [message #389546 is a reply to message #389209] Mon, 02 March 2009 10:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> I've been messing round with "returning" but not getting much to happen.
Then you are not doing it correctly.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

>result = con.writeStatement.executeUpdate(SQL);
What ends up in "result" after the call completes?
Re: Trouble with trigger [message #389547 is a reply to message #389545] Mon, 02 March 2009 11:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I suggest you do it in a stored procedure.

You can pass whatever paremeters you need to the Oracle procedure and send back to Java whatever you need with IN/OUT parameters.

Example

Re: Trouble with trigger [message #389549 is a reply to message #389209] Mon, 02 March 2009 11:04 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
result just gets the number of rows updated.

For the stored procedure - can this be called as a trigger?
EDIT: I've just noticed your example, I'll take a look.

[Updated on: Mon, 02 March 2009 11:06]

Report message to a moderator

Re: Trouble with trigger [message #389584 is a reply to message #389545] Mon, 02 March 2009 14:26 Go to previous messageGo to next message
sam_jk
Messages: 7
Registered: February 2009
Junior Member
Okay, I've done some searching on Google and there are no examples I can find of returning a value for a trigger, but it is frequently mentioned that this can be done with a trigger. In the below code I first assign DFPR001 based on a sequence. I am trying to bring back a value DFPR001 from DFPR, that has just been assigned by a sequence.

I'm getting Oracle errors for this one, but can't figure out the issue. It was working until I added in the return lines.

From what I understand, the 2nd line sets up "id" which is the same format as DFPR001. THe 5th line returns. I'm not sure what line 6 does exectly - but I found this line in other return statements for SQ functions. Can anyone see the issue?

declare 
id DFPR.DFPR001%TYPE; 
begin 
select DFPR_seq.nextval into :new.DFPR001 from dual; 
returning DFPR001 into id; 
dbms_output.put_line(id);
end;
Re: Trouble with trigger [message #389585 is a reply to message #389584] Mon, 02 March 2009 14:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"I'm getting Oracle errors" is useless.

WHICH Oracle Errors do you get?

And doing this with a trigger is the wrong way. Do everything you need to do in a procedure that you call from Java.

Forget about triggers for this requirement, they don't fit. You can't "return a value" from a trigger, at least not in the way you seem to need it.

And you can look up what every statement does in the Oracle Documentation Search Page

The example you posted could be something that returns a value to a shell script, but it will not work that way in Java.
Re: Trouble with trigger [message #389683 is a reply to message #389585] Tue, 03 March 2009 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This has all got very complicated, and I'm no longer at all sure what the OP's system does, or why.

I am assuming that you are populating the PK value with a sequence in some fashion (Trigger, function call, etc) that means that you do not know what the value inserted it.

If this is so, there are two ways to get the PK value back:

1) SELECT sequence_name.currval
INTO my_variable
FROM dual;

This will return the last value that was returned by the sequence

2) Change the insert to use the RETURNING clause

INSERT INTO my_table (column_list)
VALUES (value_list)
RETURNING pk_column INTO my_variable;

This will return whatever value finally ended up in the column PK_column into the local pl/sql variable my_variable.


That block of code you pasted is a badly confused mishmash of these two solutions.
Re: Trouble with trigger [message #389870 is a reply to message #389683] Tue, 03 March 2009 19:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The usual method for implementing an auto-incrementing key in Oracle is to use a sequence and a before insert row trigger and I have demonstrated this below, although some people prefer to do this within a procedure. The only problem with using a procedure instead of a trigger is that inserts with wrong numbers can be done from outside the procedure, like through SQL*Plus. Your usage of a before update row trigger to prevent update of the id once it has been inserted is fine. Your insertion of a blank record, followed by an update sounds unnecessary. If you are just talking about one table, then you should just use one insert statement. However, if you have related tables and you want to insert a record into a parent table, return the auto-incrementing value form the id and insert that same id into a child table, which is what I am guessing you are trying to do, then I have demonstrated that below. I have allowed the value to be inserted through the trigger and just returned it, but you could eliminate the trigger and insert the sequence value in the procedure if you like.

-- parent and child tables for demo:
SCOTT@orcl_11g> CREATE TABLE pppp
  2    (pppp_id     NUMBER PRIMARY KEY,
  3  	pppp_data   VARCHAR2 (30))
  4  /

Table created.

SCOTT@orcl_11g> CREATE TABLE other_table
  2    (pppp_id     NUMBER REFERENCES pppp (pppp_id),
  3  	other_data  VARCHAR2 (30))
  4  /

Table created.


-- sequence and trigger to automatically increment primary key:
SCOTT@orcl_11g> CREATE SEQUENCE pppp_seq
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pppp_insert
  2    BEFORE INSERT ON pppp
  3    FOR EACH ROW
  4  DECLARE
  5  BEGIN
  6    SELECT pppp_seq.NEXTVAL INTO :NEW.pppp_id FROM DUAL;
  7  END pppp_insert;
  8  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- trigger to prevent update of primary key:
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pppp_update
  2    BEFORE UPDATE ON pppp
  3    FOR EACH ROW
  4  BEGIN
  5    IF :NEW.pppp_id <> :OLD.pppp_id OR :NEW.pppp_id IS NULL THEN
  6  	 RAISE_APPLICATION_ERROR (-20004, 'Cannot modify auto-increment field');
  7    END IF;
  8  END pppp_update;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- procedure to insert data into parent and child tables, using sequence from primary key of parent table for foreign key of child table:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE insert_data
  2    (p_ppp_data   IN VARCHAR2,
  3  	p_other_data IN VARCHAR2)
  4  AS
  5    v_id		pppp.pppp_id%TYPE;
  6  BEGIN
  7    INSERT INTO pppp (pppp_data) VALUES (p_ppp_data) RETURNING pppp_id INTO v_id;
  8    INSERT INTO other_table (pppp_id, other_data) VALUES (v_id, p_other_data);
  9  END insert_data;
 10  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- inserts and results:
SCOTT@orcl_11g> EXECUTE insert_data ('something', 'stuff')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXECUTE insert_data ('something else', 'more stuff')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM pppp
  2  /

   PPPP_ID PPPP_DATA
---------- ------------------------------
         1 something
         2 something else

SCOTT@orcl_11g> SELECT * FROM other_table
  2  /

   PPPP_ID OTHER_DATA
---------- ------------------------------
         1 stuff
         2 more stuff


-- update of primary key is not allowed:
SCOTT@orcl_11g> UPDATE pppp SET pppp_id = 3 WHERE pppp_id = 2
  2  /
UPDATE pppp SET pppp_id = 3 WHERE pppp_id = 2
       *
ERROR at line 1:
ORA-20004: Cannot modify auto-increment field
ORA-06512: at "SCOTT.PPPP_UPDATE", line 3
ORA-04088: error during execution of trigger 'SCOTT.PPPP_UPDATE'


SCOTT@orcl_11g> UPDATE pppp SET pppp_id = NULL WHERE pppp_id = 2
  2  /
UPDATE pppp SET pppp_id = NULL WHERE pppp_id = 2
       *
ERROR at line 1:
ORA-20004: Cannot modify auto-increment field
ORA-06512: at "SCOTT.PPPP_UPDATE", line 3
ORA-04088: error during execution of trigger 'SCOTT.PPPP_UPDATE'


SCOTT@orcl_11g> 






Re: Trouble with trigger [message #390303 is a reply to message #389209] Thu, 05 March 2009 16:06 Go to previous message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member



Barbara

Yet again, elegantly detailed examples.
Previous Topic: Confused need a query (Merged)
Next Topic: pass parameters to declaration section (merged)
Goto Forum:
  


Current Time: Wed Dec 07 10:34:22 CST 2016

Total time taken to generate the page: 0.14013 seconds