At the end of the code is written to execute the CREATE trigger command MCQ

Following quiz provides Multiple Choice Questions (MCQs) related to PL/SQL. You will have to read all the given answers and click over the correct answer. If you are not sure about the answer then you can check the answer using Show Answer button. You can use Next Quiz button to check new set of questions in the quiz.

plsql_questions_answers.htm

Database triggers – Learn when and how trigger works on database tables. Also Learn their classification and usage in different scenarios with examples

Database Triggers

Triggers are stored programs containing set of PL-SQL statements , which are automatically executed when ever an DML statement performed on the table.

It is a PL-SQL blok like a procedure , to perform some specific task. But a procedure always requires an explicit call for execution , but triggers are executed automatically when any triggering event occurs.

It is associated with a Table or View.

INSERT, UPDATE, DELETE are considered as triggering events of the database trigger. These events initiate the firing of trigger.

The firing of trigger is nothing but the execution of the PL-SQL code associated to that trigger.It is also a Database object.

Notes

Triggers are, in fact, written to be executed in response to any of the following events –

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Advantages

• A database trigger is a security object to provide security to the table like tracking the transaction.
• A database trigger is also used to define the complex business constraints that cannot be defined using integrity constraints.
• Automatically generating values for derived columns or PRIMARY KEY columns.
• Used to implement user defined restrictions on table.
• Provides high security.
• Activated when table are manipulated from other application software also.

Syntax for Creating Triggers

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

NOTES

Trigger cannot be duplicate trigger name.
Trigger can be attached one table.
When condition is true, Trigger will be executed otherwise not executed.

CREATE [OR REPLACE] TRIGGER trigger_name – Creates or replaces an existing trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} – This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This specifies the DML operation.
[OF col_name] – This specifies the column name that will be updated.
[ON table_name] – This specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n] – This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] – This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

WHEN (condition) – This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

NEW and OLD- refers to the available in DML statements. Valid in row trigger only.

The following points need to be considered here –

• OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.
• If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
• The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.

Application Trigger

It is fired When DML Event occurs within the Application.

Example : Oracle Forms.

Database Trigger

It is fired when DML Event occurs on a Table/ View no matter which user is connected.

Components of Trigger

Trigger Parts

Indicates when to activate the trigger. i.e defines whether the trigger first before or after the statement is executed.

Which statement is used to create a trigger Mcq?

Which command is used to create a trigger?

Which trigger execute any action after the triggering statement is executed?

How many create trigger statement Are there Mcq?