Triggers (events)

Active databases- where data maintenance is automatically performed

Passsive database - where scripts are used to maintain the database manually

Create Trigger (basic)

CREATE TRIGGER triggerName
 {BEFORE | AFTER | INSTEAD OF}
 {EVENT}
 ON {TABLENAME | VIEWNAME}

BEFORE

Runs before the event triggering the event

Cannot trigger other triggers

After event

Runs after the event causeing the trigger

Can trigger other triggers

# when incert do this....
CREATE OR REPLACE TRIGGER triName
	AFTER INCERT ON tableName
	BEGIN
		#CODE HERE
	END;
/

# multiple trigger events
CREATE OR REPLACE TRIGGER triName
	AFTER INCERT OR DELETE ON tableName
	BEGIN
		#CODE HERE
	END;
/

Events

# examples
UPDATE OF colName ON tableName
UPDATE ON tableName
INSERT ON tableName
DELETE ON tableName

ROW-BASED triggers

:new.colName - values after an event

:old.colName - values before an event (doesnt work with before (obviously))

# example
CREATE TRIGGER triName
AFTER INSERT ON tableName
FOR EACH ROW
BEGIN
	SELECT * FROM tableName
	WHERE colName = :new.colName;
END;
/

Update

CREATE TRIGGER trigName
AFTER INSERT ON tableName
FOR EACH ROW
BEGIN
	UPDATE tableName SET colName = colName + 1
	WHERE tableName.colName = :new.colName;
END;
/

When

CREATE TRIGGER trigName
AFTER INSERT OR DELETE ON tableName
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
	# code
WHEN DELETING THEN
	# code
WHEN (old.colName > new.colName) # note here : is not needed
	# assignment
	:new.colname := :old.colname;
END CASE;
END;
/