-- on-line documentation of PostgresQL triggers:
-- https://www.postgresql.org/docs/9.1/sql-createtrigger.html

-- executes as rps programmer

\pset pager off

/*
    A database trigger is procedural code that is automatically
    executed in response to certain events on a particular table
    or view in a database. The trigger is mostly used for maintaining
    the integrity of the information on the database.
    
    CREATE TRIGGER fooTrigger
    ON tableName
    [AFTER/  (BEFORE/INSTEAD OF) ] [INSERT/UNPDATE/DELETE]
    do something
*/

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; -- Log the attempt and abort.
END $$;

-- -------------------------------

-- CREATE OR REPLACE TRIGGER is not implemented

DROP TRIGGER IF EXISTS preventDeleteOnlog ON tblPlayersLog;

CREATE TRIGGER preventDeleteOnlog
BEFORE DELETE ON tblPlayersLog
	FOR EACH ROW
  	EXECUTE PROCEDURE prohibitDeleteOnlog();

-- test it -----------------------
/*
        DELETE FROM tblRounds WHERE r_id=8;
*/    
-- -------------------------------
    
    
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; -- allow update to proceed
END $$;

-- -------------------------------
DROP TRIGGER IF EXISTS ChangesOnRounds;

CREATE TRIGGER ChangesOnRounds
BEFORE UPDATE ON tblRounds
	FOR EACH ROW
  	EXECUTE PROCEDURE logChangesOnRounds();

-- --------------------------------
/*
        UPDATE tblRounds
        SET r_token2='P'
        WHERE r_id=8;
*/        

-- -----------------------------

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 $$;

-- -----------------------------

--Implement a trigger that will intercept DELETE on the tblPlayers table and, BEFORE deleting the record, will set the p_active field to FALSE.
DROP TRIGGER IF EXISTS DeleteOnPlayers;

CREATE TRIGGER DeleteOnPlayers
BEFORE DELETE ON tblPlayers
	FOR EACH ROW
  	EXECUTE PROCEDURE AlterDeleteOnPlayers();


--Implement two triggers that will block DELETE on tblGames and tblRounds respectively.
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();


--Implement a trigger that will log updates on tblGames. (You will create the log table as demonstrated in the notes.)
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; -- allow update to proceed
END $$;

DROP TRIGGER IF EXISTS ChangesOnTblGames;

CREATE TRIGGER ChangesOnTblGames
BEFORE UPDATE ON tblGames
	FOR EACH ROW
  	EXECUTE PROCEDURE logChangesOnTblGames();

PGSQL Triggers

SELECT
	pka,
	c1,
	pkb,
	c2
FROM
	A
INNER JOIN B ON pka = fka;

PGSQL Triggers

More PostgreSQL Posts

PostgreSQL INNER JOIN