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.