| 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'
|
|
|
|
| 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: 68765
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
