74 lines
2.2 KiB
PL/PgSQL
74 lines
2.2 KiB
PL/PgSQL
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(); |