You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table.
Which two statements are correct about the trigger to be created for the above requirement? (Choose two.)
A.
It should be an after trigger.
B.
It should be a before trigger.
C.
It should be a row-level trigger.
D.
It should be a statement-level trigger.
E.
It can be a before or an after trigger.
“that displays the number of rows remaining in the table” …
and
“It should be a row-level trigger”
Why not statement-level trigger?
Test case:
CREATE TABLE test_tbl(id NUMBER, val VARCHAR2(10))
/
CREATE OR REPLACE TRIGGER ROW_TRIGGER
AFTER DELETE ON TEST_TBL
FOR EACH ROW
declare
v_cnt number;
begin
select count(*) into v_cnt
from TEST_TBL;
DBMS_OUTPUT.Put_Line(v_cnt);
end;
/
INSERT INTO test_tbl(“ID”,”VAL”) VALUES (1,’val1′)
/
INSERT INTO test_tbl (“ID”,”VAL”) VALUES(2,’val2′)
/
commit
/
delete from test_tbl where id = 1;
ORA-04091: table TEST_TBL is mutating, trigger/function may not see it ORA-06512: at “ROW_TRIGGER”, line 4 ORA-04088:
error during execution of trigger ‘ROW_TRIGGER’
but statement-level trigger is success works!!! where am I wrong?
I agree with OldBoyOdeSu answer is A and D. If it is row level trigger you will always get ORA-04091 mutating table error as you are trying to perform a select on a table that is not read consistent. A row level trigger can not read or write from the table on which it has been fired.
Yes, it should be a statement level trigger. another reason is that if it is a row level trigger, it can be after or before trigger.
Before: select count(*)-1 into variable. This way also can show the remaining rows if row level trigger can work.
It should be an after statement trigger.
After Row trigger will give mutating table error. Although it can be avoided by PRAGMA AUTONOMOUS_TRANSACTION. Answer should be A and D.
A D
A%D
a, c
CREATE or replace TRIGGER test_count_tr
after delete ON test FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_count number;
BEGIN
select count(*)-1 into v_count from test;
dbms_output.put_line(‘count:’||v_count);
–COMMIT; — allowed only in autonomous triggers
END;
In this version TRIGGER test_count_tr prints on each deleted row the same value because the parent transaction is only commited when all rows are deleted – so answer C is false
A, D – my choice
A,D
A,C is correct
because he is say “display the number of rows remaining in the table” , that mean can’t display the number when use before ,because he do not how rows will be deleted
thanks for all
A, D
A,D
AD
A,C are correct
because when it before how can collect remaining record and i don’t know which or how many records will be deleted
another reason
when it statement level that means not necessary using before or after
and in choices have not “without before or after ”
Thanks for all
AD is correct answer
Always a massive fan of linking to bloggers that I love but dont get a good deal of link adore from.
please check out the web sites we comply with, such as this 1, as it represents our picks in the web
below youll discover the link to some websites that we believe you’ll want to visit
Always a large fan of linking to bloggers that I appreciate but dont get lots of link appreciate from.
below youll come across the link to some sites that we believe you should visit
we prefer to honor numerous other web web-sites around the net, even though they arent linked to us, by linking to them. Under are some webpages really worth checking out
check below, are some entirely unrelated websites to ours, having said that, they’re most trustworthy sources that we use
the time to study or stop by the subject material or websites we’ve linked to below the
that will be the end of this article. Here you will find some web-sites that we consider you will appreciate, just click the hyperlinks over
Here are a number of the internet sites we advocate for our visitors
check below, are some absolutely unrelated sites to ours, nevertheless, they’re most trustworthy sources that we use
Wonderful story, reckoned we could combine a couple of unrelated information, nonetheless truly really worth taking a look, whoa did one particular find out about Mid East has got far more problerms as well
we came across a cool web-site that you just might love. Take a look in case you want
ad
For those of you that did not recognize that C was trap, you should study mutating table in a trigger and use of compound triggers.