DROP TRIGGER trig_use_dir_inser_before ON t_dav_use_dir;
-- DROP TRIGGER trig_use_dir_update_before ON t_dav_use_dir;
DROP FUNCTION use_dir_trigger();

CREATE FUNCTION use_dir_trigger() RETURNS TRIGGER AS $use_dir_trigger$
DECLARE
	cid			integer;
	pdirid		bigint;
	ppath 		text;
	patharry	text[];
BEGIN
	-- root path
	IF NEW.depth <= 0 THEN 
		RETURN NEW;
	END IF;	
	
	-- parent path 
	patharry := string_to_array(NEW.path_name, '/');

	ppath := '';
	FOR cid IN 0 .. (NEW.depth - 1)
	LOOP
		ppath := ppath || '/';
		ppath := ppath || patharry[cid+2];
	END LOOP;

	SELECT dir_id INTO pdirid FROM t_dav_use_dir WHERE depth = (NEW.depth - 1) AND path_name = ppath;

	IF (TG_OP = 'INSERT') THEN
		-- RAISE NOTICE  'pdir_id - %, parent path - %', pdirid, ppath;

		IF pdirid IS NULL THEN
			BEGIN
				IF (NEW.depth - 1) > 0 THEN
					INSERT INTO t_dav_use_dir (sp_user_seq, sp_svc_tran_id, dir_pid, depth, path_name, dir_used) 
					SELECT NEW.sp_user_seq, NEW.sp_svc_tran_id, 0, (NEW.depth - 1), ppath, 0;
				ELSE 
					INSERT INTO t_dav_use_dir (sp_user_seq, sp_svc_tran_id, depth, path_name, dir_used) 
					SELECT NEW.sp_user_seq, NEW.sp_svc_tran_id, 0, ppath, 0;
				END IF;
			EXCEPTION
				WHEN unique_violation OR undefined_column THEN
					-- do nothing
			END;
			
			SELECT dir_id INTO pdirid FROM t_dav_use_dir WHERE depth = (NEW.depth - 1) AND path_name = ppath;			
		END IF ;

		IF pdirid IS NOT NULL THEN
			NEW.dir_pid = pdirid;
		END IF ;
	/*
	ELSIF (TG_OP = 'UPDATE') THEN
		IF ( TG_WHEN = 'BEFORE' ) THEN
			-- RAISE NOTICE  'UPDATE BEFORE pdir_id - %, parent path - %, used = %', pdirid, ppath, NEW.dir_used;
			IF pdirid IS NOT NULL THEN
				UPDATE t_dav_use_dir SET dir_used = dir_used +( NEW.dir_used - OLD.dir_used ) 
				WHERE depth = (NEW.depth - 1) AND path_name = ppath;
			END IF;			
		END IF;	
	--*/	
	END IF; 

	RETURN NEW;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE NOTICE  'use_dir_trigger EXCEPTION';
		RETURN NEW;
END;
$use_dir_trigger$ LANGUAGE 'plpgsql';


CREATE TRIGGER trig_use_dir_inser_before BEFORE INSERT ON t_dav_use_dir FOR EACH ROW EXECUTE PROCEDURE use_dir_trigger();
-- CREATE TRIGGER trig_use_dir_update_before BEFORE UPDATE ON t_dav_use_dir FOR EACH ROW EXECUTE PROCEDURE use_dir_trigger();