Home » SQL & PL/SQL » SQL & PL/SQL » How to print message like :no of rows are deleted from emp"
How to print message like :no of rows are deleted from emp" [message #247285] Mon, 25 June 2007 06:36 Go to next message
pvr_msg
Messages: 7
Registered: August 2006
Location: hyderabad
Junior Member
HI to everybody

How to write a Trigger the given below mentioned message


I have one emp table whenever there is any update or delete or insert in that emp table i want a message like "no of rows updated" or "no of rows deleted" or "no of rows are inserted".



example: suppose emp table 100 rows are there
when i issue "delete from emp where deptno=10"(assume thatIt deletes 20 rows delted)

20 rows are deleted.

This message i want in that particular table



Re: How to print message like :no of rows are deleted from emp" [message #247287 is a reply to message #247285] Mon, 25 June 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This message comes from SQL*Plus checking the cursor attribute %ROWCOUNT.
You can do the same thing.
SQL> select count(*) from t;
  COUNT(*)
----------
         1

1 row selected.

SQL> begin
  2    delete t;
  3    dbms_output.put_line(SQL%ROWCOUNT||' row(s) deleted');
  4  end;
  5  /
1 row(s) deleted

PL/SQL procedure successfully completed.

Regards
Michel
Re: How to print message like :no of rows are deleted from emp" [message #247446 is a reply to message #247287] Mon, 25 June 2007 22:55 Go to previous messageGo to next message
pvr_msg
Messages: 7
Registered: August 2006
Location: hyderabad
Junior Member
Thanks for reply

But it is not working in Trigger.

Re: How to print message like :no of rows are deleted from emp" [message #247448 is a reply to message #247285] Mon, 25 June 2007 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>But it is not working in Trigger.
Who ever said it would work within a trigger.

A trigger runs inside the database & does not know or care about your terminal.
Re: How to print message like :no of rows are deleted from emp" [message #247452 is a reply to message #247446] Mon, 25 June 2007 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In a trigger, you are in the process of updating/deleteting... How can it know how rows will be updated/deleted as it has not finished the statement?

Regards
Michel
Re: How to print message like :no of rows are deleted from emp" [message #247476 is a reply to message #247446] Tue, 26 June 2007 01:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pvr_msg wrote on Tue, 26 June 2007 05:55
But it is not working in Trigger.
You are saying: "I can't get it to work"

I don't know why you would do it, but for me it works quite well:

SQL> CREATE TABLE mhe_foo(col1 NUMBER)
  2  /

Table created.

SQL>
SQL> INSERT INTO mhe_foo
  2  SELECT level
  3  FROM  dual
  4  CONNECT BY LEVEL < 121
  5  /

120 rows created.

SQL>
SQL> CREATE TRIGGER mhe_asd
  2  AFTER DELETE ON mhe_foo
  3  BEGIN
  4    dbms_output.put_line(SQL%ROWCOUNT);
  5  END;
  6  /

Trigger created.

SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> DELETE FROM mhe_foo
  2  WHERE col1 > 100
  3  /

20 rows deleted.

SQL>
SQL> DROP TABLE mhe_foo
  2  /

Table dropped.

SQL>


MHE
Re: How to print message like :no of rows are deleted from emp" [message #247482 is a reply to message #247476] Tue, 26 June 2007 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But what happens if another trigger fires after that and raise an error rolling back the statement?
Does dbms_output transactional? That is are the messages erase from dbms_output buffer?

Just questions. I didn't check it.

Regards
Michel
Re: How to print message like :no of rows are deleted from emp" [message #247484 is a reply to message #247482] Tue, 26 June 2007 01:38 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I was wondering the same thing, Michel. If I can, and it would be my call, I'd use as little triggers as possible and I'd put all trigger code in packages. Triggers have no predefined firing sequence. I've seen applications with 3 triggers of the same type on a table. Which one fires first? I couldn't tell.

MHE

[Updated on: Tue, 26 June 2007 01:39]

Report message to a moderator

Previous Topic: Working with CLOB
Next Topic: Both SAP and IMS replies are invoking the same function in stored procedure so need to synchronise
Goto Forum:
  


Current Time: Wed Dec 07 06:50:00 CST 2016

Total time taken to generate the page: 0.10589 seconds