OraFAQ Forum: SQL & PL/SQL » SQL%ROWCOUNT USing in INSERT or update trigger

Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT USing in INSERT or update trigger (Oracle 10G windows )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL%ROWCOUNT USing in INSERT or update trigger [message #574383]

Wed, 09 January 2013 15:51

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'

Report message to a moderator

Re: SQL%ROWCOUNT USing in INSERT or update trigger [message #574387 is a reply to message #574383]

Wed, 09 January 2013 18:46

dariyoosh
Messages: 538
Registered: March 2009
Location: 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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Query help
Next Topic:	Filter Special Characters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 12 14:23:19 CDT 2025