CREATE TRIGGER trg_line_prod
ON LINE
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
	DECLARE @P_CODE CHAR(8)
	DECLARE @TOTAL INT
--Insert Case
IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
BEGIN
	DECLARE INSERTED_CURSOR CURSOR FOR
	SELECT P_CODE, SUM(LINE_UNITS) AS TOTAL
	FROM INSERTED
	GROUP BY P_CODE

	OPEN	INSERTED_CURSOR
	FETCH	NEXT FROM INSERTED_CURSOR
				INTO @P_CODE, @TOTAL
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
			UPDATE PRODUCT
			SET		P_QOH = P_QOH - @TOTAL
			WHERE	P_CODE = @P_CODE
			FETCH NEXT FROM INSERTED_CURSOR
				INTO @P_CODE, @TOTAL
	END
	CLOSE INSERTED_CURSOR
	DEALLOCATE INSERTED_CURSOR
END
--Delete Case
IF(EXISTS (SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED))
BEGIN
	DECLARE DELECTED_CURSOR CURSOR FOR
	SELECT P_CODE, SUM(LINE_UNITS) AS TOTAL
	FROM DELETED
	GROUP BY P_CODE

	OPEN	DELETED_CURSOR
	FETCH	NEXT FROM DELETED_CURSOR
					INTO @P_CODE, @TOTAL
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
			UPDATE PRODUCT
			SET		P_QOH = P_QOH + @TOTAL
			WHERE	P_CODE = @P_CODE
			FETCH	NEXT FROM DELETED_CURSOR
						INTO @P_CODE, @TOTAL
	END
	CLOSE DELETED_CURSOR
	DEALLOCATE DELETED_CURSOR
END
--Update Case
IF(EXISTS (SELECT * FROM DELETED) AND EXISTS (SELECT  * FROM INSERTED))
BEGIN
		DECLARE UPDATE_CURSOR CURSOR FOR
		SELECT I.P_CODE, SUM(I.LINE_UNITS - D.LINE_UNITS) AS TOTAL
		FROM DELETED D INNER JOIN INSERTED I ON D.P_CODE = I.P_CODE AND 
		D. INV_NUMBER = I.INV_NUMBER
		GROUP BY I.P_CODE

		OPEN	UPDATE_CURSOR
		FETCH	NEXT FROM UPDATE_CURSOR
						INTO @P_CODE, @TOTAL
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
				UPDATE	PRODUCT
				SET		P_QOH = P_QOH - @TOTAL
				WHERE	P_CODE = @P_CODE
				FETCH	NEXT FROM UPDATE_CURSOR
								INTO @P_CODE, @TOTAL
				END
				CLOSE UPDATE_CURSOR
				DEALLOCATE UPDATE_CURSOR
	END
END