Which two statements are true about triggers? (Choose two.)
A.
All the triggers that are created on a table cannot be disabled simultaneously.
B.
Any user who has the alter privilege on a table can create a trigger using that table.
C.
Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.
D.
Triggers become invalid if a dependent object, such as 3 stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation.
answer is C & D
Right answer is B and D.
1) A is incorrect;
http://www.techonthenet.com/oracle/triggers/disable_all.php
ALTER TABLE table_name DISABLE ALL TRIGGERS;
2) C is incorrect;
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm
uses two-phase commit if a trigger updates remote tables in a distributed database
!!!no in local database!!!
I checked and reread again every answer. Here right answer only D…
1) A is incorrect there is no doubts
2) B is Incorrect too.
Any (not schema owner) user can’t create trigger on table if he have only alter privilege. To do this he needs create ANY trigger privilege. I checked it
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2003
Excerpt:
To create a trigger in another schema, or to reference a table in another schema from a trigger in your schema:
You must have the CREATE ANY TRIGGER system privilege.
You must have the EXECUTE privilege on the referenced subprograms or packages.
My test:
a) Grant alter privilige
connect hr/hr;
grant alter on employees to dw;
b) Checking priviliges for user dw
connect dw/dw;
SELECT * FROM USER_TAB_PRIVS;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
DW HR EMPLOYEES HR ALTER NO NO
c) Creating trigger on hr.employees from user dw
connect dw/dw;
CREATE OR REPLACE TRIGGER tr_after_log_on
after logon on schema
Declare
v_num number := 1;
BEGIN
select
hr.employees.employee_id
into
v_num
from
hr.employees;
END;
Result is
ERROR: PL/SQL: ORA-01031: insufficient privileges
3) D is incorrect but on a half
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm
a) true part
For enabled triggers, Oracle automatically:
uses two-phase commit if a trigger updates remote tables in a distributed database
b) false part
But there is only “remote tables in a distributed database” no local database mentioned.
4) D is correct.
To avoid exception on first call after modifications we need to recomple it manually
(if we don’t do this it will be recompiled automatically on next call)
great answer, but I think B) is assuming that you have “create all trigger” priviledge since you have to choose 2 answers.
hi Uladzimir, i’m Piero
about your useful link… on questio C……….
here what’s written , it seems correct,
what did you means with !!!no in local database!!! ?
thanks
Regards
————————————————————–
For enabled triggers, Oracle automatically
executes triggers of each type in a planned firing sequence when more than one trigger is fired by a single SQL statement
performs integrity constraint checking at a set point in time with respect to the different types of triggers and guarantees that triggers cannot compromise integrity constraints
provides read-consistent views for queries and constraints
manages the dependencies among triggers and objects referenced in the code of the trigger action
uses two-phase commit if a trigger updates remote tables in a distributed database
if more than one trigger of the same type for a given statement exists, Oracle fires each of those triggers in an unspecified order
ahhhh i’ve understood,
yes you are right
there is written OR
in local database OR remote databse.
you are right, the answer C is not correct.
in local databse there isn’t a double phase commit, absolutely not.
then the two right answer must be B and D also if it is nor specified if the user has or not the system privilege create trigger…..
REgards
Wait, you wrong about C, man.
In a distribuite transaction you can do update on a local database or remote database and in both cases there will be a two phase commit!
Don’t look at “database local” in the question, just look that you are in a distribuited database so there is on Transaction Coordinator which execute the two phases.
C is correct. Bye
are you sure the ans is C & D
yes answer is c & d
B D looks the correct ans to me
B & D
C & D
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm
Triggers “have to be manually recompiled before the next invocation”?
It’s doubtfully because Oracle can implement validation implicitly when trigger is invoked, no manual recompilation is needed.
On the other hand, C also seems wrong.
My verdict: maybe this is one of so called “unscored” questions 🙂
it seems that it would be better manually recompile an invalid trigger caused by to avoid a time consuming event at run time.
but also that Oracle always attempts to recompile obsolete units.
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/REC1/Default.aspx
what is wrong and what is right ?
so this is my doubt ?????
a and b are seems really wrong so……
but d is right ?????
anyone can help us ?
“uses two-phase commit if a trigger updates remote tables in a distributed database”
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm
C is wrong
thats the point. oracle says: the db uses a two-phase commit if a trigger updates remote tables in a distributed database.
The question was: Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.
So oracle says: only on distributed databases and c says: on _BOTH_ local and distributed. c) must be wrong.
Regarding the create trigger privileges from the oracle db:
———————————
To create a trigger in your schema:
You must have the CREATE TRIGGER system privilege <++++++++++++++
One of the following must be true: <————–
You own the table specified in the triggering statement
You have the ALTER privilege for the table specified in the triggering statement <————–
You have the ALTER ANY TABLE system privilege
To create a trigger in another schema, or to reference a table in another schema from a trigger in your schema:
You must have the CREATE ANY TRIGGER system privilege.
You must have the EXECUTE privilege on the referenced subprograms or packages.
———————————
"You must have the CREATE TRIGGER system privilege" the oracle doc says. But a) is not true, too you can disable all triggers with one alter statement.
“updates tables in the local database”
That could be interpreted as “distributed transactions” too, no? That’s the only way C could be correct.
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_tr.htm
Privileges Needed to Work with Triggers
To create a trigger in your schema, you must have the CREATE TRIGGER system privilege, and either:
Own the table specified in the triggering statement, or
Have the ALTER privilege for the table in the triggering statement, or
Have the ALTER ANY TABLE system privilege
To create a trigger in another user’s schema, or to reference a table in another schema from a trigger in your schema, you must have the CREATE ANY TRIGGER system privilege. With this privilege, the trigger can be created in any schema and can be associated with any user’s table. In addition, the user creating the trigger must also have EXECUTE privilege on the referenced procedures, functions, or packages.
To create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER privilege. If this privilege is later revoked, then you can drop the trigger, but not alter it.
The object privileges to the schema objects referenced in the trigger body must be granted to the trigger owner explicitly (not through a role). The statements in the trigger body operate under the privilege domain of the trigger owner, not the privilege domain of the user issuing the triggering statement. This is similar to the privilege model for stored procedures.
B is wrong cause of what is typed in your link!
CREATE [ANY] TRIGGER is mandatory then you should have on of
1) Own the table specified in the triggering statement, or
2) Have the ALTER privilege for the table in the triggering statement, or
3) Have the ALTER ANY TABLE system privilege
In the question B says that you need just the Alter Table and this is not true!
bye
Answer : B &C
A is Wrong.
B is correct
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_tr.htm
Privileges Needed to Work with Triggers
To create a trigger in your schema, you must have the CREATE TRIGGER system privilege, and either:
Own the table specified in the triggering statement, or
Have the ALTER privilege for the table in the triggering statement, or
Have the ALTER ANY TABLE system privilege
C is correct
http://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
Oracle uses two-phase commit if a trigger updates remote tables in a distributed database.
D is Wrong
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_tr.htm
Dependencies for Triggers
Compiled triggers have dependencies. They become invalid if a depended-on object, such as a stored procedure or function called from the trigger body, is modified. Triggers that are invalidated for dependency reasons are recompiled when next invoked.
D in fact says “before the next invocation”. Before you need to compile the trigger manually.
So it’s right…
B is wrong,
— USER SQL
ALTER USER “trigger_test” IDENTIFIED BY oracle
DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”
ACCOUNT UNLOCK
ENABLE EDITIONS ;
— SYSTEM PRIVILEGES
GRANT CREATE TABLE TO “trigger_test” ;
GRANT ALTER ANY TABLE TO “trigger_test” ;
GRANT CREATE SESSION TO “trigger_test” ;
GRANT ALTER SESSION TO “trigger_test” ;
–create and change table
create table tt1(col1 number);
alter table tt1 add (col2 number); –no problem to change it
alter table
CREATE OR REPLACE TRIGGER tt1_trigger_1
BEFORE UPDATE
ON tt1
DECLARE
vMsg VARCHAR2(30) := ‘Statement Level Trigger Fired’;
BEGIN
dbms_output.put_line(vMsg);
END tt1_trigger_1;
/
–ORA-01031
yes B is wrong
SQL> CREATE USER scot IDENTIFIED BY tiger;
User created
SQL> GRANT CREATE SESSION TO scot;
Grant succeeded
SQL> GRANT create ANY table,alter ANY Table TO scot;
Grant succeeded
SQL> GRANT UNLIMITED TABLESPACE TO scot;
Grant succeeded
SQL> connect scot/tiger;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scot
SQL>
SQL> CREATE OR REPLACE TRIGGER drop_trigger
2 after logon ON SCHEMA
3 BEGIN
4 dbms_output.put_line ( ‘ logged in’); END;
5 /
CREATE OR REPLACE TRIGGER drop_trigger
after logon ON SCHEMA
BEGIN
dbms_output.put_line ( ‘ logged in’); END;
ORA-01031: insufficient privileges
SQL> create table emp(id number);
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER id_trig
2 after insert on emp
3 for each row
4 BEGIN
5 dbms_output.put_line ( ‘ linserted’); END;
6 /
CREATE OR REPLACE TRIGGER id_trig
after insert on emp
for each row
BEGIN
dbms_output.put_line ( ‘ linserted’); END;
ORA-01031: insufficient privileges
connect to sys
SQL> grant CREATE ANY TRIGGER to scot;
Grant succeeded
SQL> connect scot/tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scot
SQL>
SQL> CREATE OR REPLACE TRIGGER id_trig
2 after insert on emp
3 for each row
4 BEGIN
5 dbms_output.put_line ( ‘ linserted’); END;
6 /
Trigger created
D IS WORNG. THE TRIGGER WILL BECOME VALID ON NEXT INVOKATION
create or replace trigger id_trig
after insert on emp
for each row
call pr_1
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
——————————————————————————– ——————- ——-
ID_TRIG TRIGGER VALID
EMP TABLE VALID
PR_1 PROCEDURE VALID
SQL>
SQL> create or replace procedure pr_1 is
2 begin
3 dbms_output.put_line(‘inside procedure’);
4 dfsdf
5 end;
6 /
Warning: Procedure created with compilation errors
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
——————————————————————————– ——————- ——-
PR_1 PROCEDURE INVALID
EMP TABLE VALID
ID_TRIG TRIGGER INVALID
SQL>
SQL> create or replace procedure pr_1 is
2 begin
3 dbms_output.put_line(‘inside procedure’);
4 end;
5 /
Procedure created
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
——————————————————————————– ——————- ——-
PR_1 PROCEDURE VALID
EMP TABLE VALID
ID_TRIG TRIGGER INVALID
SQL> insert into emp values(2);
1 row inserted
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
——————————————————————————– ——————- ——-
PR_1 PROCEDURE VALID
EMP TABLE VALID
ID_TRIG TRIGGER VALID
SQL>
Hi Friends,
i am going to take up the test .please help with the valid dumps.
please mail to [email protected]
thanks in advance.
According to Oracle only B is correct?
Can anyone pls confirm?
All answers (A,B,C,D) are wrong. So i think the question (Which two statements are true about triggers? (Choose two.)) is wrong.
Sorry. Answer is B and C
C,D
No doubt.
C and D.
all are wrong
cd
plss reply the correct ans …. i m confuse to read all ur review
That is the best search engine in the planet
http://google.com