Database Programming with PL/SQL
13-5: Managing Triggers
Practice Activities
Try It / Solve It
1. Query the data dictionary to display information about all the triggers you created in earlier practices. The
query should display the names, triggering event, trigger type, kind of object the trigger is on (table, view
etc), name of the table or view, and the status.
SELECT trigger_name, triggering_event, trigger_type, base_object_type, table_name, status
FROM USER_TRIGGERS;
2. Disable one of your triggers and re-query the data dictionary view. What is the trigger’s new
status?
ALTER TRIGGER count_trigger DISABLE;
SELECT trigger_name, triggering_event, trigger_type, base_object_type, table_name, status
FROM USER_TRIGGERS
WHERE trigger_name = 'COUNT_TRIGGER';
The status is now DISABLED
3. List the source code of the trigger that you just disabled.
SELECT trigger_body
FROM USER_TRIGGERS
WHERE trigger_name = 'COUNT_TRIGGER';
4. Remove the trigger from the database and re-query the data dictionary to check that it has been
removed.
DROP TRIGGER count_trigger;
SELECT *
FROM USER_TRIGGERS
WHERE trigger_name = 'COUNT_TRIGGER';
5. Create a stored procedure that re-enables all disabled triggers in your schema.
A. The procedure should declare a cursor as:
SELECT…FROM user_triggers…WHERE …;
CREATE OR REPLACE PROCEDURE enable_proc IS
CURSOR trig_cur IS
SELECT trigger_name
FROM USER_TRIGGERS
WHERE status = 'DISABLED';
trig_rec trig_cur%ROWTYPE;
sql_stmt VARCHAR2(200);
BEGIN
FOR trig_rec IN trig_cur LOOP
sql_stmt := 'ALTER TRIGGER ' || trig_rec.trigger_name || ' ENABLE';
EXECUTE IMMEDIATE sql_stmt;
END LOOP;
END enable_proc;
B. Disable two of your triggers, then execute the procedure you created in step 5A.
ALTER TRIGGER max_sal DISABLE;
ALTER TRIGGER log_emp DISABLE;
BEGIN
enable_proc;
END;
C. Look at the data dictionary again to check that the triggers have been re-enabled.
SELECT trigger_name, status
FROM USER_TRIGGERS
WHERE trigger_name IN ('MAX_SAL', 'LOG_EMP');
both are enabled.
0 Comments