Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT USing in INSERT or update trigger (Oracle 10G windows )
SQL%ROWCOUNT USing in INSERT or update trigger [message #574383] Wed, 09 January 2013 15:51 Go to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Hi,
I am using below trigger and update statement but it is updating 10 records without rasing any error. Please advise. I am trying to get the rowcount and rasing an error if it is more than one.

CREATE OR REPLACE TRIGGER test_tr
AFTER INSERT OR UPDATE
ON test
FOR EACH ROW
DECLARE
v_rows NUMBER(12);
v_dt_str CHAR(20);

BEGIN
v_rows := SQL%ROWCOUNT ;
IF v_rows = 0 THEN
RETURN;
END IF;
v_dt_str := SYSDATE ;
-- Insert or Update only one row at a time
DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||
v_rows);

IF v_rows > 1 THEN

BEGIN
raise_application_error( -20002, 93002||':'||v_dt_str||'; ERROR; Only one row can be processed at a time!' );
ROLLBACK;
utils.resetTrancount;
RETURN;
END;
END IF;
END;
/

I am updating all the records of the table but it is not raising any error..
update test set name ='testing'
Re: SQL%ROWCOUNT USing in INSERT or update trigger [message #574387 is a reply to message #574383] Wed, 09 January 2013 18:46 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
A FOR EACH ROW trigger is fired once for each row affected by the DML, therefore one row processed at a time! As a result v_rows > 1 will never be true within your trigger.

In addition, DBMS_OUTPUT.PUT_LINE inside the trigger is useless as the trigger is executed at the server side, you will need to update a log table or a log file.

What do you want to do exactly? particularly if we are talking about this problem within a multi-user environment?


Regards,
Dariyoosh

[Updated on: Wed, 09 January 2013 18:52]

Report message to a moderator

Re: SQL%ROWCOUNT USing in INSERT or update trigger [message #574406 is a reply to message #574387] Thu, 10 January 2013 01:32 Go to previous message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
s a result v_rows > 1 will never be true within your trigger.


As well as v_rowsz=0, as if there is no rows to insert or update the trigger will not fire.

Quote:
In addition, DBMS_OUTPUT.PUT_LINE inside the trigger is useless as the trigger is executed at the server side,


Although it is right the trigger execute in server side (as ALL SQL or PL/SQL code), it is not useless, the result is displayed by the client (if it supports and has activated the feature).
In the end, it is a very BAD practice to use dbms_output but for debugging purpose.

@skumari

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

If you want to insert or update one row at a time (why? I don't see any reason for this but to have bad performances), then test %ROWCOUNT at application level.

Regards
Michel
Previous Topic: Query help
Next Topic: Filter Special Characters
Goto Forum:
  


Current Time: Sun Nov 23 02:55:16 CST 2014

Total time taken to generate the page: 0.10302 seconds