13 - Oracle Sequence and trigger

What are the NEW and OLD Pseudo-records?

OLD and NEW are psedo-records that the PL/SQL runtime engine creates and populates whenever a row-level trigger fires.These store the original and new values of the record being processed by the trigger. They are called psedo-records because they do not have all properties of PL/SQL records.

The events that can be exceuted by triggers are database manipulation statement (DML), database definiton statement (DDL), and database operation.

Name the events which can be executed by using triggers.

pseudo-records

OLD and NEW are ________ that the PL/SQL runtime engines creates and populates whenever a row-level trigger fires.

sequence

PL/SQL unit that can be used to insert unique values in Primary Key and Unique Key columns of tables

sequence

PL/SQL unit that can create an autonumber

database operations

SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN

starts with

specify the first sequence number to be generated

increment by

specify the interval between sequence numbers

Triggers

stored programs, which are automatically executed or fired when some events occur

DROP TRIGGER trigger-name;

syntax to delete trigger

ALTER TRIGGER trigger-name enable;

syntax to enable trigger

database manipulation statement, database definition statement, database operations

triggers are executed in response to these events

positive value

value for increment by clause when it ascends

negative value

value for increment by clause when it descends

WHEN (condition)

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

[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.

{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.

{BEFORE | AFTER | INSTEAD OF }

This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.

executed, fired

Triggers are stored programs, which are automatically _______ or ______ when some events occur.

sequences

We can create an autonumber field by using ________.

The benefits of triggers are that enforces referential integrity, event logging and storing information on table access, auditing, synchronous replication of tables, imposing security authorizations, preventing invalid transactions.

What are the three benefits of triggers?

A sequence is an object in Oracle used to generate a number sequence.

What is a sequence in Oracle database?

An Oracle trigger is a stored programs, which are automatically executed or fired when some event occurs

What is an Oracle trigger?

DROP SEQUENCE stud_cin_seq;

Write a command and remove the stud_cin_seq from the database.

ALTER TRIGGER stud_trigg enable;

Write a query and enable the disabled trigger stud_trigg.

DBA_triggers

_______ view describes all triggers in the database.

starts with

________ specifies the first sequence number to be generated.

starts with

clause used to start either An ascending sequence at a value greater than its minimum, OR Start a descending sequence at a value less than its maximum

DBA_TRIGGERS

describes all triggers in the database

28 or fewer

digits that an integer value of the starts with and increment by clause can have

DROP SEQUENCE sequence_name;

drop a sequence syntax

minimum value of the sequence

in starts with clause, the default value for ascending sequences

maximum value of the sequence

in starts with clause, the default value for descending sequences

disabled

"ALTER trigger trig_empno enable;" is a command that we apply when the status of trig_empno is ________.

false. Triggers

(T/F) Auto numbers are stored programs, which are automatically executed or fired when some events occur.

true

(T/F) They are called psudo-records because they do not have all properties of PL/SQL records.

true

(T/F) Triggers can be written for preventing invalid transactions.

false. cannot be 0

(T/F) When creating a sequence the number value of "increment by" can be any positive or negative integer, but it can be 0.

false. 28

(T/F) When creating a sequence the number value of "increment by" can have 26 or fewer digits.

New and OLD Pseudo-records

- OLD and NEW are pseudo-records that the PL/SQL runtime engine creates and populates whenever a row-level trigger fires. - OLD and NEW store the original and new values, respectively, of the record being processed by the trigger. - They are called pseudo-records because they do not have all properties of PL/SQL records.

benefits of triggers

- enforcing referential integrity, keep the values of foreign keys in line with those in primary keys - event logging and storing information on table access - auditing - synchronous replication of tables - imposing security authorizations - preventing invalid transactions

DROP

A trigger is deleted with the _______ statement.

sequence

An object in Oracle that is used to generate a number sequence

database definition (DDL) statement

CREATE, ALTER, or DROP

increase the readability

Comment can be used in the program to ________ of the program.

CREATE [OR REPLACE ] TRIGGER trigger_name

Creates or replaces an existing trigger with the trigger_name.

Database manipulation (DML) statement

DELETE, INSERT, or UPDATE

[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.