🔥 Trigger Basics
CREATE TRIGGER Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
[FOR EACH ROW|STATEMENT]
BEGIN
-- Trigger logic
END trigger_name;
/Trigger Types Comparison
| Trigger Timing | Level | Use Case |
|---|---|---|
| BEFORE ROW | Before each row change | Data validation, default values |
| AFTER ROW | After each row change | Audit trails, cascading updates |
| BEFORE STATEMENT | Before DML statement | Batch validation, permissions check |
| AFTER STATEMENT | After DML statement | Logging, external notifications |
📝 Practical Trigger Examples
BEFORE ROW INSERT Trigger
CREATE OR REPLACE TRIGGER validate_employee
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
-- Check salary range
IF :NEW.salary < 30000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary too low');
END IF;
-- Set default hire date if not provided
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := TRUNC(SYSDATE);
END IF;
END validate_employee;
/AFTER ROW UPDATE Trigger (Audit Trail)
CREATE OR REPLACE TRIGGER audit_emp_changes
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
-- Log changes to audit table
INSERT INTO emp_audit_log(
emp_id, field_name, old_value, new_value, changed_date
) VALUES (
:OLD.employee_id,
'SALARY',
:OLD.salary,
:NEW.salary,
SYSDATE
);
COMMIT;
END audit_emp_changes;
/INSTEAD OF Trigger on View
CREATE OR REPLACE TRIGGER emp_view_insert
INSTEAD OF INSERT
ON emp_view
FOR EACH ROW
BEGIN
INSERT INTO employees(first_name, salary, department_id)
VALUES (:NEW.first_name, :NEW.salary, :NEW.dept_id);
END emp_view_insert;
/Useful Trigger Commands
-- Disable trigger
ALTER TRIGGER trigger_name DISABLE;
-- Enable trigger
ALTER TRIGGER trigger_name ENABLE;
-- Disable all triggers on table
ALTER TABLE table_name DISABLE ALL TRIGGERS;
-- Drop trigger
DROP TRIGGER trigger_name;
-- View trigger status
SELECT trigger_name, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';⚡ Trigger Best Practices
- Keep trigger logic simple and focused
- Avoid complex business logic in triggers
- Use BEFORE ROW for validation and defaults
- Use AFTER ROW for audit trails and cascading
- Handle errors with proper exception handling
- Be careful with COMMIT in triggers
- Document trigger purpose and behavior
- Monitor trigger performance impact
💡 Tip: Use :NEW to reference new values, :OLD for previous values. ROW-level triggers
execute once per row. STATEMENT-level triggers execute once per statement. Be cautious with cascading
triggers.
✓ Learning Checklist - Module 11