trigger.sql

CREATE OR REPLACE FUNCTION check_trigger_data()
RETURNS TRIGGER LANGUAGE python AS
$python$
import Postgres

def check_td(td, orientation, timing, dm):
	assert td.args == ('arg1','arg2','arg3')
	assert td.orientation == orientation
	assert td.timing == timing
	assert td.manipulation == dm
	assert td.trigger_name.startswith("trigger_checker_")
	assert td.trigger_catalog == Postgres.current_database
	assert td.table_catalog == Postgres.current_database
	assert td.trigger_schema == td.table_schema
	assert td.trigger_schema == "public"
	assert td.table_name == "wood"
	assert isinstance(td.type, Postgres.Type)
	assert td.type.typname == "wood"
	assert len(td.type.descriptor) == 1
	assert str(td.type(('reltype',))[0]) == 'reltype'

def before_delete(td, old):
	check_td(td, "ROW", "BEFORE", "DELETE")

def after_delete(td, old):
	check_td(td, "ROW", "AFTER", "DELETE")

def before_insert(td, new):
	check_td(td, "ROW", "BEFORE", "INSERT")

def after_insert(td, new):
	check_td(td, "ROW", "AFTER", "INSERT")

def before_update(td, old, new):
	check_td(td, "ROW", "BEFORE", "UPDATE")

def after_update(td, old, new):
	check_td(td, "ROW", "AFTER", "UPDATE")

## statement level ##

def before_truncate_statement(td):
	check_td(td, "STATEMENT", "BEFORE", "TRUNCATE")

def after_truncate_statement(td, olds):
	assert olds is None
	check_td(td, "STATEMENT", "AFTER", "TRUNCATE")

def before_delete_statement(td):
	check_td(td, "STATEMENT", "BEFORE", "DELETE")

def after_delete_statement(td, olds):
	assert olds is None
	check_td(td, "STATEMENT", "AFTER", "DELETE")

def before_insert_statement(td):
	check_td(td, "STATEMENT", "BEFORE", "INSERT")

def after_insert_statement(td, news):
	assert news is None
	check_td(td, "STATEMENT", "AFTER", "INSERT")

def before_update_statement(td):
	check_td(td, "STATEMENT", "BEFORE", "UPDATE")

def after_update_statement(td, olds, news):
	assert olds is None
	assert news is None
	check_td(td, "STATEMENT", "AFTER", "UPDATE")
$python$;

CREATE OR REPLACE FUNCTION stop_event()
RETURNS TRIGGER LANGUAGE python AS
$python$
from Postgres import StopEvent
def f(*args):
	raise StopEvent()
before_insert = before_delete = before_update = f
after_insert = after_delete = after_update = f
$python$;

CREATE OR REPLACE FUNCTION pass()
RETURNS TRIGGER LANGUAGE python AS
$python$
def f(*args):
	pass
before_insert = before_delete = before_update = f
after_insert = after_delete = after_update = f
$python$;

CREATE OR REPLACE FUNCTION return_new()
RETURNS TRIGGER LANGUAGE python
AS $python$
def before_update(td, old, new):
	return new

def before_insert(td, new):
	return new
$python$;

CREATE OR REPLACE FUNCTION return_old()
RETURNS TRIGGER LANGUAGE python AS
$python$
def before_update(td, old, new):
	return old

def before_delete(td, old):
	return old
$python$;

CREATE OR REPLACE FUNCTION raise_error()
RETURNS TRIGGER LANGUAGE python AS
$python$
def f(*args):
	raise ValueError("dirty data. dirty")

before_insert = before_update = before_delete = \
after_insert = after_update = after_delete = f
$python$;

CREATE OR REPLACE FUNCTION insert_instead ()
RETURNS TRIGGER LANGUAGE python AS
$python$
def before_update(td, *args):
	return ("instead",)
after_update = after_insert = before_insert = before_update
$python$;

CREATE TABLE wood (info text);
INSERT INTO wood VALUES ('already there');
SELECT * FROM wood ORDER BY info;

--

CREATE TRIGGER dontdelete
 BEFORE DELETE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE stop_event();
DELETE FROM wood;
DROP TRIGGER dontdelete ON wood;

SELECT *, 'should exist' FROM wood ORDER BY info;

--

CREATE TRIGGER dodelete
 BEFORE DELETE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE pass();
DELETE FROM wood;
DROP TRIGGER dodelete ON wood;

SELECT *, 'FAILURE WHEN ROWS PRESENT' FROM wood ORDER BY info;

--

INSERT INTO wood VALUES ('already there');

CREATE TRIGGER dodelete
 BEFORE DELETE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE return_old();

DELETE FROM wood;
DROP TRIGGER dodelete ON wood;

SELECT *, 'FAILURE WHEN ROWS PRESENT' FROM wood ORDER BY info;

--

CREATE TRIGGER raise_error
 BEFORE INSERT ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE raise_error();
CREATE TRIGGER insert_instead
 BEFORE INSERT ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE insert_instead();

INSERT INTO wood VALUES ('should never exist');
DROP TRIGGER insert_instead ON wood;
DROP TRIGGER raise_error ON wood;

SELECT * FROM wood ORDER BY info;

--

INSERT INTO wood VALUES ('not updated');
CREATE TRIGGER dont_update
 BEFORE UPDATE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE stop_event();
UPDATE wood SET info = 'updated' WHERE info = 'not updated';
DROP TRIGGER dont_update ON wood;

-- Should show "info: updated"
SELECT * FROM wood ORDER BY info;

--

-- Test return_new on UPDATE
CREATE TRIGGER do_update
 BEFORE UPDATE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE return_new();
UPDATE wood SET info = 'updated' WHERE info = 'not updated';
DROP TRIGGER do_update ON wood;

-- Should show "info: updated"
SELECT * FROM wood;

--

-- Test different tuple returned on UPDATE
CREATE TRIGGER do_update_instead
 BEFORE UPDATE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE insert_instead();
UPDATE wood SET info = 'SHOULD NOT BE SET' WHERE info = 'updated';
DROP TRIGGER do_update_instead ON wood;

-- Should show "info: instead"
SELECT * FROM wood;

-- AFTER --

-- Can't stop events afterwards
CREATE TRIGGER try_not_delete
 AFTER DELETE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE stop_event();
DELETE FROM wood;
DROP TRIGGER try_not_delete ON wood;

-- Ignored
CREATE TRIGGER try_insert_instead
 AFTER INSERT ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE insert_instead();
INSERT INTO wood VALUES ('SHOULD EXIST');
DROP TRIGGER try_insert_instead ON wood;

-- Ignored
CREATE TRIGGER try_update_instead
 AFTER UPDATE ON wood
 FOR EACH ROW
  EXECUTE PROCEDURE insert_instead();
UPDATE wood SET info = 'SHOULD BE UPDATED';
DROP TRIGGER try_update_instead ON wood;

--
-- Check Postgres.TriggerData --
--

CREATE TRIGGER trigger_checker_befores
 BEFORE DELETE OR UPDATE OR INSERT ON wood
 FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3');

CREATE TRIGGER trigger_checker_afters
 AFTER DELETE OR UPDATE OR INSERT ON wood
 FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3');

CREATE TRIGGER trigger_checker_statement_befores
 BEFORE DELETE OR UPDATE OR INSERT OR TRUNCATE ON wood
 FOR STATEMENT EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3');

CREATE TRIGGER trigger_checker_statement_afters
 AFTER DELETE OR UPDATE OR INSERT OR TRUNCATE ON wood
 FOR STATEMENT EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3');

-- Run outside & inside a transaction and, for paranoia, inside a savepoint.
-- The trigger data is cached, so it's important to identify that new trigger
-- data is being constructed for each orientation, timing, and manipulation.

INSERT INTO wood VALUES ('foo');
UPDATE wood SET info = 'bar' WHERE info = 'foo';
DELETE FROM wood;
TRUNCATE wood;

BEGIN;
INSERT INTO wood VALUES ('foo');
UPDATE wood SET info = 'bar' WHERE info = 'foo';
DELETE FROM wood;
TRUNCATE wood;
COMMIT;

BEGIN;
SAVEPOINT a;
INSERT INTO wood VALUES ('foo');
UPDATE wood SET info = 'bar' WHERE info = 'foo';
DELETE FROM wood;
TRUNCATE wood;
RELEASE a;
COMMIT;

DROP TRIGGER trigger_checker_statement_afters ON wood;
DROP TRIGGER trigger_checker_statement_befores ON wood;
DROP TRIGGER trigger_checker_afters ON wood;
DROP TRIGGER trigger_checker_befores ON wood;

-- validate that trigger name is being validated
-- "trigger_checker" is off by one character, '_'
CREATE TRIGGER trigger_checker
 BEFORE DELETE OR UPDATE OR INSERT ON wood
 FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2','arg3');

INSERT INTO wood VALUES ('WRONG');
DROP TRIGGER trigger_checker ON wood;

-- validate that trigger args are being checked
-- the check function expects three arguments: arg1, arg2, and arg3
CREATE TRIGGER trigger_checker_
 BEFORE DELETE OR UPDATE OR INSERT ON wood
 FOR EACH ROW EXECUTE PROCEDURE check_trigger_data('arg1','arg2');

INSERT INTO wood VALUES ('WRONG');
DROP TRIGGER trigger_checker_ ON wood;