1. What is a System Event Trigger in Oracle?
A System Event Trigger is a trigger that automatically fires in response to database-level or schema-level events, not table DML operations.
It responds to events such as:
- Database startup or shutdown
- User logon or logoff
- DDL operations (CREATE, ALTER, DROP)
- Server errors
2. What is the difference between a System Trigger and a DML Trigger?
|
Feature |
System Event Trigger |
DML Trigger |
|
Fires On |
Database/System Events |
INSERT/UPDATE/DELETE |
|
Scope |
Database or Schema |
Table or View |
|
Use Case |
Audit DDL, log logins |
Audit row changes |
3. What are the types of System Event Triggers?
Database-Level Triggers
Fire for events affecting the entire database.
Examples:
- AFTER STARTUP ON DATABASE
- BEFORE SHUTDOWN ON DATABASE
- AFTER LOGON ON DATABASE
- AFTER DDL ON DATABASE
- AFTER SERVERERROR ON DATABASE
Schema-Level Triggers
Fire for events affecting a specific schema.
Example:
- AFTER DDL ON HR.SCHEMA
4. How do you create a System Event Trigger?
Example: Log user logins
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO login_audit (username, logon_time)
VALUES (USER, SYSDATE);
END;
/
5. What privileges are required?
- For database-level triggers:
- ADMINISTER DATABASE TRIGGER
- For schema-level triggers:
- CREATE TRIGGER
6. What events can System Triggers respond to?
Common system events include:
- AFTER STARTUP
- BEFORE SHUTDOWN
- AFTER LOGON
- BEFORE LOGOFF
- AFTER DDL
- AFTER SERVERERROR
- AFTER SUSPEND
7. Can System Event Triggers prevent operations?
Yes. You can block certain operations using RAISE_APPLICATION_ERROR.
Example: Prevent DROP statements
CREATE OR REPLACE TRIGGER prevent_drop
BEFORE DROP ON DATABASE
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'DROP operation is not allowed.');
END;
/
8. How can you audit DDL statements?
CREATE OR REPLACE TRIGGER ddl_audit
AFTER DDL ON DATABASE
BEGIN
INSERT INTO ddl_log
VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_NAME, USER, SYSDATE);
END;
/
Useful built-in functions:
- ORA_SYSEVENT
- ORA_DICT_OBJ_NAME
- ORA_LOGIN_USER
- ORA_DICT_OBJ_TYPE
9. Where are System Event Triggers stored?
In data dictionary views:
- DBA_TRIGGERS
- ALL_TRIGGERS
- USER_TRIGGERS
10. Can System Triggers affect performance?
Yes, especially:
- Heavy logic inside AFTER LOGON
- Complex processing in AFTER DDL
- Recursive DDL inside triggers
Best practice: Keep logic lightweight and efficient.
11. What are common mistakes?
- Missing required privileges
- Causing recursive DDL loops
- Blocking SYS or system users unintentionally
- Not handling exceptions
- Using COMMIT/ROLLBACK unnecessarily
12. Can System Triggers fire for specific object types only?
Yes, by filtering inside the trigger:
IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
-- logic here
END IF;
13. Can a trigger fire on database startup?
Yes.
Example:
CREATE OR REPLACE TRIGGER startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('Database Started');
END;
/
14. Can you disable or drop a System Event Trigger?
Disable:
ALTER TRIGGER trigger_name DISABLE;
Enable:
ALTER TRIGGER trigger_name ENABLE;
Drop:
DROP TRIGGER trigger_name;
15. Are System Event Triggers supported in all Oracle versions?
They are supported in Oracle 8i and later versions, with additional features and improvements in newer releases.
No comments:
Post a Comment