Question For Trigger

PL/SQL interview question for Trigger

1. What is Trigger? how many type of trigger ?
2. Why we will use trigger?
3. Mutating in Trigger.
4. Can we write trigger over view? Write one program to update view.
5. What is the use of INSTEAD OF in trigger?
6. What is Compound trigger and what is the use of compound Trigger?
7. What is the use of FOLLOWS clause or how to declare the order of trigger?
8. Normal trigger execution order .
9. Difference between ROW level and Statement level trigger?
10. Difference between BEFORE and AFTER trigger?
11. How can we improve the performance of a trigger ?
12. What is a CALL statement? Explain with an example.What is a CALL statement? Explain with an example.
13. What is the difference between database trigger and stored procedure?
14. COMMIT statement can be executed as part of a trigger?



Ans

1. A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Type OF Trigger:

i. Application Trigger:

 Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

ii. Database trigger: 

These triggers are fired whenever a data event (such as DML,DDL) or system event (such as logon or shutdown) occurs on a schema or database.

2. Advantages Of Database Trigger:

Improve Data Security:

• Provide enhanced and complex security checks
• Provide enhanced and complex auditing

Improve Data integrity:

• Enforce dynamic data integrity constraints
• Enforce complex referential integrity constraints
• Ensure that related operations are performed together implicitly

3. Mutating in Trigger:

4. Yes, we can write trigger over view.

5.

11. The performance of a trigger can be improved by using column names along with the UPDATE clause in the trigger. This will make the trigger fire when that particular column is updated and therefore, prevents unnecessary action of trigger when other columns are being updated.

12. A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The procedure may be in PL/SQL, C, or Java language. 

Following is an example of the CALL statement:

CREATE OR REPLACE TRIGGER [trigger_name]

BEFORE UPDATE OF [column_name]  ON [table_name]
FOR EACH ROW
WHEN [condition_clause]
CALL [procedure_name]

13. 

• The main difference between database trigger and stored procedure is that the trigger is invoked implicitly and stored procedure is invoked explicitly.
• Transaction Control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, are not allowed within the body of a trigger whereas, these statements can be included in a stored procedure.

14. No, A COMMIT statement cannot be executed as a part of a trigger because it is a Transaction Control statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its code.

WIPRO

1.can we use select statement in trigger.
2. how can we retrieve last n records
3.select clause'clauses order'means where,order by, group by..
4. write the query to get maximum salary in each department.
5. table emp has 20 records
select count(8) from emp,
 select count(2) from emp,  
select count(1) from emp,
select count(*) from emp. 
what is the output.
6. can we use commit in trigger.
7.where clause and having clause difference.
8. not null vs zero.

Review Questions – Chapter 10

                                     Review Questions – Chapter 10

1.	Which of the following assertions most correctly describes the privileges in force after the SQL below is executed? connect athos/musketeer grant select,insert,update,delete on    athos.services to porthos    with grant option; grant all on athos.services to aramis; connect porthos/musketeer grant select,delete,insert,update on    athos.services to aramis    with grant option; connect athos/musketeer revoke all on athos.services from aramis; A.    Aramis can create an index on athos.services. B.    Aramis has no privileges on athos.services. C.    Aramis can select from athos.services. D.   Aramis can select, insert, update, and delete rows from athos.services.
2.	Which of the following assertions most correctly describes the privileges in force after the SQL below is executed? connect system/manager grant select any table to jon with admin option; grant select any table to jason; connect jon/seekrit grant select any table to jason; revoke select any table from jason; A.    Jason can select from any table regardless of any individual table privileges. B.    Jason can only select from tables that he has been granted SELECT privileges on or has acquired via a role. C.    Jason can only select from his own tables. D.   Jason continues to enjoy the SELECT ANY TABLE privilege.
3.	You need to create a database-authenticated account named selena. This account should have the password welcome, and Selena should be required to change this password as soon as she connects. Which of the following SQL statements most completely meets these requirements? A.    create user selena password welcome expired; B.    create user selena identified by welcome expire; C.    create user selena identified by welcome expire password; D.   create user selena identified by welcome password expire;
4.	You have an account called sales that owns the tables for an application. You have created the tables and need to ensure that no one will be able to connect as this account. Which of the following SQL statements most completely meets these requirements? A.    alter user sales account lock; B.    alter user sales disable account; C.    alter user sales lock account; D.   alter account sales lock;
5.	Which of the following queries will include the privileges on another user's procedure that you have granted to a third party? A.    SELECT owner, proc_name, grantor, grantee FROM all_sql_privs; B.    SELECT owner, sql_name, grantor, grantee FROM all_sql_privs; C.    SELECT owner, table_name, grantor, grantee, privilege FROM all_tab_privs_made; D.   SELECT owner, sql_name, grantor, grantee FROM user_table_privs;
6.	You have a few developers who insist on connecting to the database as the well-known table-owning account HR, which is reserved for system testing. These developers need to periodically connect to the HR account to promote changes, but the corporate guidelines say that development should be done in each of the developer's personal accounts so they don't conflict with each other. The development manager has asked you to enable any database settings that might help discourage these developers from all connecting to the HR account at the same time. Which of the following options will best assist the development manager? A.    Give the development manager SELECT privileges on the V$SESSION table, so she can monitor her team's connection activity. B.    Lock the HR account and make the developers come to a DBA when they need to promote changes to system test. C.    Use a profile to limit the number of concurrent sessions for user HR to one. D.   Create an after logon trigger that causes the logon to fail if someone else is logged into the HR account.
7.	Which of the following actions cannot be done with an ALTER USER statement? A.    Expire a password. B.    Enable DBA privileges. C.    Set the default tablespace for tables. D.   Set different default tablespaces for indexes and tables.
8.	Which init.ora parameter will limit the number of concurrent session from non-DBA accounts to 16? A.    sessions=16 B.    license_max_sessions =16 C.    processes=16 D.   max_concurrent_logons=16
9.	What cannot be done with a profile? A.    Limit the number of physical reads per session to 100,000. B.    Limit the number of logical reads per session to 1,000,000. C.    Limit passwords to expire after 90 days. D.   Limit the duration of each session to 9 hours.
10.	Which of the following assertions most correctly describes the privileges in force after the SQL below is executed? connect system/manager grant dba to arsal with admin option; grant dba to gretchen; connect arsal/troodon grant dba to gretchen; revoke dba from gretchen; A.    Gretchen can exercise DBA privileges. B.    Gretchen can grant DBA privileges to other accounts. C.    Arsal loses DBA privileges. D.   Gretchen loses DBA privileges.
11.	Which statement will configure the principle_user profile to lock any account after three failed logon attempts? A.    alter profile principle_user set failed_logon_attempts=3; B.    alter profile principle_user limit failed_logon_attempts 3; C.    alter principle_user profile set failed_logon_attempts=3; D.   alter profile principle_user lock account when failed_logon_attempts=3; E.    You can't limit failed logon attempts.
12.	Which of the following SQL statements will give user Nikki the privileges to assign SELECT authority on HR.EMPLOYEES to other user accounts? A.    grant select on hr.employees to nikki; B.    grant select on hr.employees to nikki with grant option; C.    grant select on hr.employees to nikki with admin option; D.   grant select on hr.employees to nikki cascade;
13.	Which statement will set a five-minute limit to the maximum time that a user with the default profile can remain idle? A.    alter user default set profile max_idle_time=300; B.    alter profile default limit max_idle_time 300; C.    alter profile default limit idle_time 5; D.   alter profile default limit idle_time 300;
14.	Which init.ora parameter will assist you in enforcing named user licensing, by limiting the number of user accounts that can be created in your database? A.    max_users B.    license_max_users C.    max_named_users D.   named_users_max
15.	Which of the following statements will give user Zachary the privilege to modify only the COMMENTS column in the CUSTOMER table? A.    grant update on customer(comments) to zachary; B.    grant update (comments) on customer to zachary; C.    grant update on customer.comments to zachary; D.   grant update on customer columns(comments) to zachary;
16.	Mary has granted INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION, and DELETE WITH GRANT OPTION privileges on the CHART_OF_ACCOUNTS table to Charlie. Charlie is changing jobs and should not have the grant option. How can Mary leave the INSERT, UPDATE, and DELETE privileges, but remove the WITH GRANT OPTION? Mary also wants to ensure that whomever Charlie granted the privileges to will retain the privileges. A.    Grant the privileges on CHART_OF_ACCOUNTS without the grant option, and then revoke the privileges WITH GRANT OPTION. B.    Simply revoke the grant option. C.    Revoke the privileges, so that the grant option goes away, and then grant the privileges without the grant option. D.   Extract all the grants that Charlie made from the data dictionary, revoke the privileges on CHART_OF_ACCOUNTS, grant the privileges on CHART_OF_ACCOUNTS without the grant option, and regrant all the extracted privileges.
17.	You need to report on all of the column privileges that you have made on your BONUS table. You must include the name of the account receiving the privilege, which column, and which privilege. Which of the following statements will accomplish this task? select grantor, table_name, column_name, privilege from user_col_privs_recd where table_name ='BONUS'; select * from all_col_privs_made where table_name='BONUS'; select table_name, column_name, privilege, grantee from user_col_privs_made where table_name ='BONUS'; select grantee, table_name, column_name, privilege from all_tab_col_privs where owner=user and table_name='BONUS';
18.	EMP is a table. Mary is a user. Sales_mgr is a role. Which one of the following statements will fail? A.    grant sales_mgr to mary with admin option; B.    grant read on emp to mary; C.    grant insert,update,delete on emp to mary with grant option; D.   grant reference on emp to mary;
19.	Which of the following table privileges cannot be granted to a role (can only be granted to a user)? A.    INDEX B.    ALTER C.    REFERENCE D.   TRUNCATE
20.	If Judy grants ALL on her table FORMAT_CODES to PUBLIC, which operation will user Jerry not be able to perform without being granted other privileges? A.    create index on judy.format_codes B.    alter table judy.format_codes C.    delete table judy.format_codes D.   truncate table judy.format_codes

Answers

1.	D. Object privileges can be obtained from more than one grantor. To completely remove object privileges from an account, all grantors must revoke these privileges. Aramis was granted the four privileges SELECT, INSERT, UPDATE, and DELETE on athos.services from Porthos, as well as ALL (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCE) from Athos. After Athos revokes the privileges that he granted, Aramis still retains the privileges that were granted from Porthos.
2.	B. Oracle does not retain the grantor on system privileges, so if anyone revokes a system privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as role privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.
3.	D. You create a database-authenticated account with the CREATE USER statement. You assign the password with the IDENTIFIED BY clause and expire the password with the PASSWORD EXPIRE clause. When the password expires, the user will be required to change it on the next connection to the database.
4.	A. To lock an account, disabling logons for that account, you alter the account with the ACCOUNT LOCK option.
5.	C. All of the other data dictionary tables are fictitious.
6.	C. This one is really tricky. All of the options would work technically. However, the development manager probably has better things to do than monitor who on her team is connecting as which user. Unless the corporate standards say a DBA must promote changes to system test, the DBA probably has better things to do than slow down the development efforts by getting involved in promotions to system test. The after logon trigger is a clever bit of engineering, but it actually does the same thing as the profile with added complexity, overhead, and maintenance.
7.	D. It would be nice, but Oracle does not (yet) let you set a default tablespace for indexes. DBA privileges can be enabled by default with an ALTER USER statement if the role was granted to the user previously and set to disabled.
8.	B. Option A is a hard limit that includes restricted session logons. The processes setting includes such non-logon processes as pmon, lgwr, and parallel I/O slaves. The max_concurrent_logons parameter is fictitious. When the number of logon sessions reaches license_max_sessions, only restricted session (DBA) logons are allowed.
9.	A. You can limit a number of resources with a profile, but the number of physical reads can be dependent on how warm the cache is and cannot be limited via a profile.
10.	D. Oracle does not retain the grantor on role privileges, so if anyone revokes a role privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as system privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.
11.	B. Know the syntax for changing resource limits in a profile.
12.	B. The WITH GRANT OPTION clause is used to give the grantee the ability to grant the privilege to other accounts. The WITH ADMIN OPTION does the same thing with system and role privileges.
13.	C. The ALTER PROFILE statement is used to change a profile, and the idle_time parameter is set in minutes, not seconds.
14.	B. license_max_users can be used to limit the number of user accounts created. The other options are fictitious.
15.	B. Any additional columns would appear as a comma-delimited list within the parentheses.
16.	D. There is no simple and easy way to remove the WITH GRANT OPTION while retaining the privilege. Revoking a privilege from someone will cascade through and revoke it from all grantees, so it would be crucial to first extract these privileges before revoking them.
17.	C. The grantee is the recipient of the privilege. Every one of the ALL_DATA dictionary views contains not only the user's own objects, but also those that the user has access to, so ALL_COL_PRIVS_MADE may contain privileges on other schemas' tables. ALL_TAB_COL_PRIVS is not a valid data dictionary view.
18.	B. The READ privilege is valid only on directories.
19.	C. TRUNCATE is not a table privilege. INDEX and ALTER can be granted to either a user or a role, but REFERENCE can be granted only to a user.
20.	D. TRUNCATE is not a table privilege.

Review Questions – Chapter 9

                                      Review Questions – Chapter 9

1.	Which statement will create a sequence that starts with 0 and gets smaller one whole number at a time? A.    create sequence desc_seq start with 0 increment by -1 maxvalue 1; B.    create sequence desc_seq increment by -1; C.    create sequence desc_seq start with 0 increment by -1; D.   Sequences can only increase.
2.	Which statement is most correct in describing what happens to a synonym when the underlying object is dropped? A.    The synonym's status is changed to INVALID. B.    You can't drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement. C.    The synonym is automatically dropped with the underlying object. D.   Nothing happens to the synonym.
3.	The built-in packaged procedure DBMS_APPLICATION_INFO.SET_MODULE has, in the package specification, the following declaration: PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE (module_name IN VARCHAR2 ,action_name IN VARCHAR2); Which of the following statements will successfully call this procedure passing 'Monthly Load' and 'Rebuild Indexes' for the MODULE_NAME and ACTION_NAME, respectively? (Choose all that apply.) dbms_application_info('Monthly Load' 'Rebuild Indexes'); dbms_application_info(  module_name=>'Monthly Load' ,action_name=>'Rebuild Indexes'); dbms_application_info('Rebuild Indexes' ,'Monthly Load'); dbms_application_info(  module_name->'Monthly Load' ,action_name->'Rebuild Indexes');
4.	With which of the following statements could you expect improved performance over a full-table scan, when a B-tree index is created on the two columns HIRE_DATE and SALARY in the HR.EMPLOYEES table? select max(salary) from hr.employees where hire_date < sysdate -90; select last_name, first_name from hr.employees where salary > 90000; update hr.employees set salary = salary * 1.05 where department_id = 102; D.   None of these statements would benefit from the index.
5.	Which of the following statements will raise an exception? A.    alter sequence emp_seq nextval 23050; B.    alter sequence emp_seq nocycle; C.    alter sequence emp_seq increment by -5; D.   alter sequence emp_seq maxvalue 10000;
6.	Rajiv has created a private synonym NEW_PRODUCTS for the MEG.PRODUCTS table. Who can select from RAJIV.NEW_PRODUCTS? A.    The users that Rajiv has granted SELECT on NEW_PRODUCTS to and Meg has granted SELECT on PRODUCTS to. B.    The users that Rajiv has granted SELECT on NEW_PRODUCTS to. C.    The users that Meg has granted SELECT on PRODUCTS to, even if Rajiv does not grant privileges to his synonym. D.   The users that Rajiv has granted SELECT on NEW_PRODUCTS to, if Meg has granted him SELECT WITH ADMIN OPTION.
7.	Which type of stored program must return a value? A.    PL/SQL procedure B.    PL/SQL function C.    Java trigger D.   Java procedure
8.	What does the following SQL statement enable all users in the database to do? create public synonym plan_table for system.plan_table; A.    Use the EXPLAIN PLAN feature of the database B.    Save execution plans in the system repository C.    Reference a table as PLAN_TABLE instead of SYSTEM.PLAN_TABLE D.   Turn on SQL tracing
9.	There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the following statements will remove this public synonym from the database? A.    drop table system.plan_table; B.    drop synonym plan_table; C.    drop table system.plan_table cascade; D.   drop public synonym plan_table;
10.	A developer reports that she is receiving the following error: SELECT key_seq.currval FROM dual; ERROR at line 1: ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined Which of the following statements does the developer need to run to fix this condition? A.    create sequence key_seq; B.    create synonym key_seq; C.    select key_seq.nextval from dual; D.   grant create sequence to public;
11.	A power user is running some reports and has asked you to put two new B-tree indexes on a large table so that her reports will run faster. You acknowledge that the indexes would speed up her reports. Can the proposed indexes slow other processes? (Choose the best answer.) A.    No, indexes only speed up queries. B.    Yes, the indexes will make the optimizer take longer to decide the best execution plan. C.    Yes, DML will run more slowly. D.   Yes, table reorganization operations will be slower.
12.	Bitmapped indexes are best suited for which type of environment? A.    High-cardinality columns B.    Online transaction processing (OLTP) applications C.    Full-table scan access D.   Low- to medium-cardinality columns
13.	The INSURED_AUTOS table has one index on the columns YEAR, MAKE, and MODEL, and one index on VIN. Which of the following SQL statements could not benefit from using these indexes? select vin from insured_autos where make='Ford' and model = 'Taurus'; select count(*) from insured_autos where make='Ford' and year = 1998; select vin from insured_autos where year = 1998 and owner = 'Dahlman'; select min(year) from insured_autos where make='Ford' and model = 'Taurus';
14.	Which clauses in a SELECT statement can an index be used for? (Choose all that apply.) A.    SELECT B.    FROM C.    WHERE D.   HAVING
15.	You need to generate artificial keys for each row inserted into the PRODUCTS table. You want the first row to use a sequence value of 1000, and you want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements? CREATE SEQUENCE product_key2 START WITH 1000 INCREMENT BY 1 NOCACHE; CREATE SEQUENCE product_key2 START WITH 1000 NOCACHE; CREATE SEQUENCE product_key2 START WITH 1000 NEXTVAL 1 NOCACHE; D.   Options A and B meet the requirements. E.    None of the above statements meet all of the requirements.
16.	Which statement will display the last number generated from the EMP_SEQ sequence? A.    select emp_seq.curr_val from dual; B.    select emp_seq.currval from dual; C.    select emp_seq.lastval from dual; D.   select last_number from all_sequences where sequence_name ='EMP_SEQ'; E.    You cannot get the last sequence number generated.
17.	Which statement will create a sequence that will rotate through 100 values in a round-robin manner? A.    create sequence roundrobin cycle maxvalue 100; B.    create sequence roundrobin cycle to 100; C.    create sequence max_value 100 roundrobin cycle; D.   create rotating sequence roundrobin min 1 max 100;
18.	The following statements are executed: create sequence my_seq; select my_seq.nextval from dual; select my_seq.nextval from dual; rollback; select my_seq.nextval from dual; What will be selected when the last statement is executed? A.    0 B.    1 C.    2 D.   3
19.	Which of the following can you not do with a package? A.    Overload procedures and functions B.    Hide data C.    Retain data across commits D.   Grant EXECUTE privileges on one procedure in a package
20.	Which of the following calls to the stored function my_sine() will raise an exception? A.    Theta := my_sine(45); B.    IF (my_sine(45) > .3 ) THEN DECLARE   Theta   NUMBER DEFAULT my_sine(45); BEGIN … D.   my_sine(45);

Answers

1.	A. For a descending sequence, the default START WITH value is -1, and the default MAXVALUE value is 0. To start the sequence with 0, you must explicitly override both of these defaults.
2.	D. Synonyms do not have a status. The CASCADE CONSTRAINTS option does not drop synonyms. Synonyms can point to nonexisting objects.
3.	B. Option A almost uses the correct positional notation, except the delimiting comma is missing. Option B uses the correct named notational style. Option C transposes the module and action name using positional notation. Option D uses the wrong assignment syntax.
4.	A. The index could be used if a leading subset of columns in the index is referenced. Options B and C do not reference the leading subset of columns in their WHERE clauses.
5.	A. You cannot explicitly change the next value of a sequence. You can set the MAXVALUE or INCREMENT BY value to a negative number, and NOCYCLE tells Oracle to not reuse a sequence number.
6.	C. Private synonyms can be referenced by anyone who has privileges on the underlying objects. You cannot grant privileges on synonyms, only on the underlying object. Option D is close, but the WITH ADMIN OPTION is only for roles and system privileges, not for table privileges.
7.	B. Functions must include a RETURN statement and must return a value.
8.	C. This statement creates a public synonym or global alias, which allows users to reference the underlying table without needing to explicitly specify the owner. A table named PLAN_TABLE is needed to use the EXPLAIN PLAN feature, but the statement above creates a public synonym. Also, the existence of a public synonym does not grant to public any privileges on the underlying object. An ALTER SESSION statement is used to enable and disable SQL tracing.
9.	D. To remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database, but will not affect any synonyms on the table.
10.	C. A sequence is not yet defined if NEXTVAL has not yet been selected from it within the current session. It has nothing to do with creating a sequence, creating a synonym, or granting privileges.
11.	C. This one's a little tricky. B, C, and D are all true, but C is the best answer. Two additional indexes should not appreciably slow the optimizer, and table reorganization in Oracle (unlike in other databases) is usually not needed. DML (INSERT, UPDATE, and DELETE) operations will definitely be slowed, as the new indexes will need to be maintained.
12.	D. Bitmapped indexes are not suited for high-cardinality columns (those with highly selective data). OLTP applications tend to need row-level locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited for multiple combinations of low- to medium-cardinality columns.
13.	A. Option A does not use a leading subset of columns in an index, nor do all of the columns come from the index. A full-table scan on the table will be needed. Options B and C use a leading subset of the three-column index, so that index could be used. Option D uses data that is found completely in the three-column index, and a full scan of this index would likely be faster than a full scan of the larger table.
14.	A, C. The obvious answer is C, but an index also can be used for the SELECT clause. If an index contains all of the columns needed to satisfy the query, the table does not need to be accessed.
15.	D. Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement.
16.	B. Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself, using the pseudo-column CURRVAL.
17.	A. The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle to. The name roundrobin is there to confuse to you.
18.	D. The CREATE SEQUENCE statement will create an increasing sequence that will start with 1, increment by 1, and be unaffected by the rollback. A rollback will never stuff vales back into a sequence.
19.	D. You can only grant EXECUTE privileges on the entire package, not on individual packaged programs.
20.	D. Functions cannot be called as stand-alone statements; only procedures can be called this way.