--
-- Name: pay_category_insert(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION pay_category_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	LeftVar int;
	levelVar INT; 
BEGIN
	IF New.PayCategoryID IS NOT NULL
	then
		IF New.ParentId IS NOT NULL
		then
			SELECT into
				leftVar, levelVar
				LeftIndex, TreeLevel
			FROM FIES.T_PAY_CATEGORY 
			WHERE PayCategoryID = New.ParentId;
			UPDATE FIES.T_PAY_CATEGORY 
			SET LeftIndex = LeftIndex + 2 
			WHERE LeftIndex > leftVar;
			UPDATE FIES.T_PAY_CATEGORY 
			SET RightIndex = RightIndex + 2 
			WHERE RightIndex > leftVar;
			UPDATE FIES.T_PAY_CATEGORY 
			SET	LeftIndex = leftVar + 1
			,	RightIndex = leftVar + 2
			,	TreeLevel = levelVar + 1
			WHERE PayCategoryID = New.PayCategoryID;
		ELSE
			UPDATE FIES.T_PAY_CATEGORY 
			SET	LeftIndex = LeftIndex + 2
			,	RightIndex = RightIndex + 2;
			UPDATE FIES.T_PAY_CATEGORY 
			SET	LeftIndex = 1
			,	RightIndex = 2
			,	TreeLevel = 0 
			WHERE PayCategoryID = New.PayCategoryID;
		END if;
	end if;
	Return New;
END;
$$;



--
-- Name: pay_category_afterinsert; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_PAY_CATEGORY') 
                     AND trigger_name = lower('pay_category_afterinsert')
                     ) 
                    THEN 
                    DROP TRIGGER pay_category_afterinsert ON FIES.T_PAY_CATEGORY;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER pay_category_afterinsert AFTER INSERT ON FIES.T_PAY_CATEGORY FOR EACH ROW EXECUTE PROCEDURE pay_category_insert();
