--Create the P_Audit after insert triggers
--Connect to db
CONNECT TO cs560

--Drop the triggers and tables if they exist
DROP TRIGGER Delete_PROJECTIONS
DROP TRIGGER Update_PROJECTIONS
DROP TRIGGER Insert_PROJECTIONS
DROP TABLE P_AUDIT

--Create the table
CREATE TABLE P_AUDIT (PROJECTION_ID INT NOT NULL, PROJECTION_NAME INT, PROJECTION_TYPE INT, SPHEROID_CODE INT, PROJECTION_UOM INT, ZONE_CODE INT, When_Changed TIMESTAMP NOT NULL, Operation CHAR(1), PRIMARY KEY (PROJECTION_ID, When_Changed))

--Create the triggers
CREATE TRIGGER Delete_PROJECTIONS AFTER DELETE ON PROJECTIONS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO P_Audit VALUES (O.PROJECTION_ID, NULL, NULL, NULL, NULL, NULL, CURRENT TIMESTAMP, 'D'); END

CREATE TRIGGER Update_PROJECTIONS AFTER UPDATE ON PROJECTIONS REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO P_Audit VALUES (N.PROJECTION_ID, N.PROJECTION_NAME, N.PROJECTION_TYPE, N.SPHEROID_CODE, N.PROJECTION_UOM, N.ZONE_CODE, CURRENT TIMESTAMP, 'U'); END

QUIT

