MIS Test 3 CH16 Triggers
trigger predicates
INSERTING, UPDATING, DELETING used to check the type of DML statement that caused the trigger to fire
when do you get NULL values for OLD and NEW
OLD - when you use it for a row that is about to be inserted bc it's not there yet NEW - when you use it for a row that is about to be deleted
correlation identifiers
OLD and NEW keywords that work with the values for the columns that are stored in the old row and the new row
how do you tell it when the trigger should be fired?
the BEFORE or AFTER keyword
for compound triggers, what do you code instead of the BEFORE or AFTER keywords?
the FOR keyword bc there can be both BEFORE and AFTER keywords
what follows the UPDATE/INSERT/DELETE part of the block?
the ON statement
what happens when you don't put the FOR EACH ROW thing
the trigger is a statement-level trigger
what can triggers do that constraints cannot?
triggers can be used to enforce rules for data consistency that can't be enforced by constraints
AFTER trigger
triggers used to store information about a statement after it executes
T/F it is possible to create a trigger that's fired when a DDL statement is executed
true
T/F you can call a procedure from inside a trigger
true
T/F you must put COMPOUND TRIGGER to identify that it is in fact a compound trigger
true
when is a trigger fired most of the time?
when an iNSERT, UPDATE, DELETE statement is executed on a table or view
what do you need if you have an UPDATE statement
you need to say UPDATE OF column_name
compound trigger
can contain blocks of PL/SQL that are executed- 1) before the triggering statement is executed 2) before/after the row is modified 3) after the triggering statement has finished executing can also share variables between these blocks of codes
FOR EACH ROW
clause that indicates that the trigger is a row-level trigger and will fire for each row that is modified
SCHEMA
fires the trigger when the DDL event occurs on the specified schema
what does adding WHEN to your FOR EACH ROW clause do
it controls when the trigger is fired put a set of parenthesis with an expression that evaluates to true or false in there
INSTEAD OF trigger
makes the views updateable by executing INSERT, UPDATE, DELETE statements on the underlying table or tables of the view instead of attempting to insert, update, delete data through the view
trigger
named block of PL/SQL code that is executed (fired) automatically when a particular type of SQL statement is executed
you can use a trigger to set a value in a new row that's about to be inserted with a value that's generated by a sequence
pg 513 idk wtf is going on
disable/enable all triggers
ALTER TABLE table_name DISABLE/ENABLE ALL TRIGGERS
syntax to disable/enable a trigger
ALTER TRIGGER trigger_name DISABLE same thing for enable just use ENABLE
renaming a trigger
ALTER TRIGGER trigger_name RENAME TO new_name
DDL events
CREATE, ALTER, DROP, GRANT, REVOKE, DDL
dropping a trigger
DROP TRIGGER trigger_name