A) Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the base table of a view are fired if an insert/update/delete statement is issued against a view.
There are two types of triggers, Statement level trigger and Row level trigger.
After /For each row
Trigger is fired/Update /
Before /For Each statement
Can we use Commit in a Database Trigger, if ‘No’ then why?
A) No. Committing in a trigger will violate the integrity of the transaction.
What is Commit, Rollback and Save point?
Commit – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks.
Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
Rollback – This statement is used to undo work.
What is the difference between DDL, DML and DCL structures?
A) DDL statements are used for defining data. Ex: Create, Alter, Drop.
DML statements are used for manipulating data. Ex: Insert, update, truncate, delete, select.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
How can u create a table in PL/SQL procedure?
A) By using execute immediate statement we can create a table in PLSQL.
Execute immediate ‘create table amit as select * from emp’;
All DDL,DML,DCL commands can be performed by using this command.