\pset pager off
CREATE OR REPLACE FUNCTION prohibitDeleteOnlog()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'Delete Prohibited';
INSERT INTO tblPlayersLog(pl_id, pl_time, pl_user, TG_OP,
p_id_OLD, p_id_NEW)
VALUES( NEXTVAL('rpsSequence'), CURRENT_TIMESTAMP,
CURRENT_USER, TG_OP, NULL, NULL);
RETURN NULL;
END $$;
DROP TRIGGER IF EXISTS preventDeleteOnlog ON tblPlayersLog;
CREATE TRIGGER preventDeleteOnlog
BEFORE DELETE ON tblPlayersLog
FOR EACH ROW
EXECUTE PROCEDURE prohibitDeleteOnlog();
CREATE OR REPLACE FUNCTION logChangesOnRounds()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'Change Logged on tblRounds.';
INSERT INTO tblPlayersLog(pl_id, pl_time, pl_user, TG_OP,
p_id_OLD, p_id_NEW)
VALUES( NEXTVAL('rpsSequence'), CURRENT_TIMESTAMP,
CURRENT_USER, TG_OP, NULL, NULL);
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS ChangesOnRounds;
CREATE TRIGGER ChangesOnRounds
BEFORE UPDATE ON tblRounds
FOR EACH ROW
EXECUTE PROCEDURE logChangesOnRounds();
CREATE OR REPLACE FUNCTION AlterDeleteOnPlayers()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'Delete on Players.';
UPDATE tblPlayers
SET p_active = FALSE
WHERE OLD.p_id = p_id;
RETURN NULL;
END $$;
DROP TRIGGER IF EXISTS DeleteOnPlayers;
CREATE TRIGGER DeleteOnPlayers
BEFORE DELETE ON tblPlayers
FOR EACH ROW
EXECUTE PROCEDURE AlterDeleteOnPlayers();
CREATE OR REPLACE FUNCTION AlterDeleteOnTblGames()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'not deleting object from tblGames.';
RETURN NULL;
END $$;
DROP TRIGGER IF EXISTS blockDeleteOnGames;
CREATE TRIGGER blockDeleteOnGames
BEFORE DELETE ON tblGames
FOR EACH ROW
EXECUTE PROCEDURE AlterDeleteOnTblGames();
CREATE OR REPLACE FUNCTION AlterDeleteOnTblRounds()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'not deleting object from tblRounds.';
RETURN NULL;
END $$;
DROP TRIGGER IF EXISTS blockDeleteOnRounds;
CREATE TRIGGER blockDeleteOnRounds
BEFORE DELETE ON tblRounds
FOR EACH ROW
EXECUTE PROCEDURE AlterDeleteOnTblRounds();
CREATE OR REPLACE FUNCTION logChangesOnTblGames()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
RAISE NOTICE 'Change Logged on tblGames.';
CREATE IF NOT EXISTS tblPlayersLog(pl_id SERIAL PRIMARY KEY, pl_time TIMESTAMP, pl_user VARCHAR(50), TG_OP VARCHAR(10), g_id_OLD INTEGER, g_id_NEW INTEGER);
INSERT INTO tblPlayersLog(pl_id, pl_time, pl_user, TG_OP,
p_id_OLD, p_id_NEW)
VALUES( NEXTVAL('rpsSequence'), CURRENT_TIMESTAMP,
CURRENT_USER, TG_OP, NULL, NULL);
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS ChangesOnTblGames;
CREATE TRIGGER ChangesOnTblGames
BEFORE UPDATE ON tblGames
FOR EACH ROW
EXECUTE PROCEDURE logChangesOnTblGames();