/* Code Fragment 9.2 Triggers for maintaining the P_TT table. */

CREATE TRIGGER INSERT_P
ON PROJECTIONS
FOR INSERT
AS
	INSERT P_TT
	SELECT *,GETDATE(),'9999-12-31'
	FROM Inserted


CREATE TRIGGER DELETE_P
ON PROJECTIONS
FOR DELETE
AS
	UPDATE P_TT
	SET Stop_Date = GETDATE()
	FROM Deleted
	WHERE P_TT.PROJECTION_ID = Deleted.PROJECTION_ID
	AND P_TT.Stop_Date = '9999-12-31'


CREATE TRIGGER UPDATE_P
ON PROJECTIONS
FOR UPDATE
AS
BEGIN
	UPDATE P_TT
	SET Stop_Date = GETDATE()
	FROM Deleted
	WHERE P_TT.PROJECTION_ID = Deleted.PROJECTION_ID
	AND P_TT.Stop_Date = '9999-12-31'

	INSERT P_TT
	SELECT *,GETDATE(),'9999-12-31'
	FROM Inserted
END
