View the Exhibit and examine the structure of the AUDIR_CUST table.
Exhibit Missing
CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.
Examine the following trigger code:
Which statement is true about the above trigger?
A.
It gives an error on compilation because it should be a statement-level trigger.
B.
It compiles and fires successfully when the credit limit is updated in the customer table.
C.
It gives an error on compilation because of the commit command in the trigger code
D.
It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER
table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in
the trigger.
B
Compiled and checked with insert
C
“Committing in a trigger is not allowed because a trigger is part
of a larger transaction.”
See:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:486421585357
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1270
I also tried this exampe (100% CURRENT CASE) and got an error:
ORA-04092: cannot COMMIT in a trigger
EDIT: D
i didn’t pay attention to the ‘it gives error on compilation’, it WILL NOT give it.
this why i chaned my opinion.
To avoid this mistake we can to specify PRAGMA AUTONOMOUS_TRANSACTION
–create table employees_hist(id number
— , prev_last_name varchar2(80)
— , cur_last_name varchar2(80));
–create or replace trigger tr_after_upd_each_row
–after update of last_name on employees
–for each row
–Begin
— insert into employees_hist (id, prev_last_name, cur_last_name)
— values (:new.employee_id, :old.last_name, :new.last_name);
— commit;
–end;
–update employees set last_name = last_name || ‘11111’
–where employee_id = 100;
–show error trigger tr_after_upd_each_row;
RESULT:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at “HR.TR_AFTER_UPD_EACH_ROW”, line 4
ORA-04088: error during execution of trigger ‘HR.TR_AFTER_UPD_EACH_ROW’
Thanks, nice explaination
SQL Statements Allowed in Trigger Bodies
The body of a trigger can contain DML SQL statements. It can also contain SELECT statements, but they must be SELECT… INTO… statements or the SELECT statement in the definition of a cursor.
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER…COMPILE are allowed.
Note:
A procedure called by a trigger cannot run the previous transaction control statements, because the procedure runs within the context of the trigger body.
Statements inside a trigger can reference remote schema objects. However, pay special attention when calling remote procedures from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote procedure is not run, and the trigger is invalidated.
D is the right answer.
http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems002.htm
CREATE TABLE anniversaries AS
SELECT DISTINCT TRUNC(hire_date) anniversary FROM employees;
ALTER TABLE anniversaries ADD PRIMARY KEY (anniversary);
CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
— Only commits the preceding INSERT, not the INSERT that fired
— the trigger.
COMMIT;
EXCEPTION
— If someone else was hired on the same day, we get an exception
— because of duplicate values. That’s OK, no action needed.
WHEN OTHERS THEN NULL;
END;
/
ok very good
B
D is the right answer, check.
It compiles successfully but will fail on update
d
result D
create table employees_hist(id number
, prev_last_name varchar2(80)
, cur_last_name varchar2(80));
create or replace trigger tr_after_upd_each_row
after update of last_name on employees
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
insert into employees_hist (id, prev_last_name, cur_last_name)
values (:new.employee_id, :old.last_name, :new.last_name);
commit;
end;
update employees set last_name = last_name || ‘11111’
where employee_id = 100;
D is the right answer.
D is correct. It compiles successfully but gives runtime error.
Ok D.
No compilation errors. But error in runtime for the commit.
What about if you don’t use PRAGMA AUTONOMOUS_TRANSACTION and commit into the trigger? I mean, just the insert into statement.
It shoud be work?
Using Autonomous Triggers
Among other things, you can use database triggers to log events transparently.
Suppose you want to track all inserts into a table, even those that roll back. In the
example below, you use a trigger to insert duplicate rows into a shadow table. Because
it is autonomous, the trigger can commit changes to the shadow table whether or not
you commit changes to the main table.
ORA-04092: cannot COMMIT or ROLLBACK in a REGULAR trigger
You tried to perform a COMMIT or ROLLBACK in a regular trigger. This is not allowed.
AUTONOMOUS_TRANSACTION Pragma
The AUTONOMOUS_TRANSACTION pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
The trigger AUDIT_CUST has Commit (TCL statement), recommend to declare PRAGMA AUTONOMOUS_TRANSACTION. The trigger will compiles successfully but will error when update.
If you want to obtain much from this piece of writing then you have to apply such methods to your won weblog.|
D
I do accept as true with all of the ideas you’ve introduced to your post. They’re really convincing and can certainly work. Still, the posts are very brief for novices. May you please lengthen them a bit from next time? Thanks for the post.|
I think that what you composed made a great deal of sense. But, what about this? suppose you composed a catchier post title? I ain’t saying your content isn’t solid, however what if you added a post title to maybe grab a person’s attention? I mean %BLOG_TITLE% is a little vanilla. You might peek at Yahoo’s home page and note how they create news titles to get people to open the links. You might try adding a video or a pic or two to grab readers excited about what you’ve got to say. Just my opinion, it would make your blog a little bit more interesting.|
No matter if some one searches for his vital thing, thus he/she wants to be available that in detail, so that thing is maintained over here.|
d
D
VMware Certified Advanced Professional 6 (Desktop and Mobility Deployment) – The industry-recognized VCAP6-DTM Deploy certification validates that you know how to deploy and optimize VMware Horizon 6 (with View) environments. It demonstrates that you have the understanding and expertise essential to leverage best practices to provide a scalable and reliable Business Mobility platform for your company. Some of the subjects include: Configuring and managing Horizon View components, configuring cloud pod archituecture, configuring Group Policy settings related to Horizon View, Configuring and optimizing desktop images for Horizon View & Mirage, Configuring and managing App Volumes AppStacks, Configuring desktop pools, Configuring and deploying ThinApp packaged applications, Configuring VMWare Identity Manager, etc.Szumigalski.com was designed by Sebastian to spread his enthusiasm for PowerShell & VMWare. Sebastian is an IT qualified professional employed in Singapore for more than fifteen years who’s at all times interested in brand-new technological innovation to hone his technical abilities & know-how. Ever since, Sebastian has become a member of PowerShell User Group & VMWare VMug group, and has been engaging in most of the meetings held in Singapore. This site will highlight just how Sebastian manage to improve some of his daily undertakings with the help of PowerShell. You will find study guides meant for the VCAP6-DTM test, which were personally created by Sebastian. Sebastian is licensed with VCAP6-DTM, and is efficient with virtualization & server maintenance from four years experience of automation. The demand for VMWare knowledgable admins and engineers are ever-increasing in today’s competitive technology marketplace. Familiarize yourself with much more about PowerShell & VMWare with Szumigalski.com!
http://www.szumigalski.com
This is the very best search engine in the world
http://google.com