Home » SQL & PL/SQL » SQL & PL/SQL » EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES?
EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230850] Fri, 13 April 2007 02:08 Go to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

Hi,

====== CREATION OF ROW LEVEL TRIGGER FOR INSERT==============
create or replace trigger aft_ins_emp1_rl
after insert on emp1
for each row
begin
dbms_output.put_line('Rows insert_row Level');
end;
/
======== CREATION OF STATEMENT LEVEL TRIGGER FOR INSERT========
create or replace trigger aft_ins_emp1_STL
after insert on emp1
begin
dbms_output.put_line('Rows insert_stmt_level');
end;
=========INSERTING THE 10 ROWS=================================
begin
for counter in 1..10
loop
insert into emp1 values(1,'','','','','','','');
end loop;
end;
========== OUTPUT==============================================

Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level
Rows insert_row Level
Rows insert_stmt_level

EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES?

Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230855 is a reply to message #230850] Fri, 13 April 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you did 10 inserts!

Regards
Michel
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230856 is a reply to message #230850] Fri, 13 April 2007 02:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well you are inserting 10 rows that's why......
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230857 is a reply to message #230856] Fri, 13 April 2007 02:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Grrr Michel you beat me too it Razz
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230858 is a reply to message #230850] Fri, 13 April 2007 02:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
INSERT INTO tbl
SELECT * FROM another_tbl_with_10_rows

would give you what you are looking for.

So would a FORALL statement in PL/SQL, but that involves the use of collections. Read the manual if you are interested.

Ross Leishman
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230870 is a reply to message #230850] Fri, 13 April 2007 02:42 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

Oh.......?

I got the difference,

An insert statement can insert only one row at a time, But

delete or update statement can delete more than one row at a time.
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230876 is a reply to message #230870] Fri, 13 April 2007 03:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
iitraghu wrote on Fri, 13 April 2007 09:42
Oh.......?

I got the difference,

An insert statement can insert only one row at a time, But

delete or update statement can delete more than one row at a time.

No. That's not right.

MHE
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #230883 is a reply to message #230870] Fri, 13 April 2007 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"insert select" can insert as many rows as you want.

Regards
Michel
Re: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES? [message #231082 is a reply to message #230883] Sat, 14 April 2007 01:21 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So can array (FORALL) INSERT.
Previous Topic: Calculated Fields and formatting
Next Topic: Can I use between and case in a where clause?
Goto Forum:
  


Current Time: Sat Dec 10 09:21:11 CST 2016

Total time taken to generate the page: 0.16797 seconds