triggers.out

关系型数据库 Postgresql 6.5.2

QUERY: create function tg_room_au() returns opaque as '
begin
    if new.roomno != old.roomno then
        update WSlot set roomno = new.roomno where roomno = old.roomno;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_room_au after update
    on Room for each row execute procedure tg_room_au();
QUERY: create function tg_room_ad() returns opaque as '
begin
    delete from WSlot where roomno = old.roomno;
    return old;
end;
' language 'plpgsql';
QUERY: create trigger tg_room_ad after delete
    on Room for each row execute procedure tg_room_ad();
QUERY: create function tg_wslot_biu() returns opaque as '
begin
    if count(*) = 0 from Room where roomno = new.roomno then
        raise exception ''Room % does not exist'', new.roomno;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_wslot_biu before insert or update
    on WSlot for each row execute procedure tg_wslot_biu();
QUERY: create function tg_pfield_au() returns opaque as '
begin
    if new.name != old.name then
        update PSlot set pfname = new.name where pfname = old.name;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_pfield_au after update
    on PField for each row execute procedure tg_pfield_au();
QUERY: create function tg_pfield_ad() returns opaque as '
begin
    delete from PSlot where pfname = old.name;
    return old;
end;
' language 'plpgsql';
QUERY: create trigger tg_pfield_ad after delete
    on PField for each row execute procedure tg_pfield_ad();
QUERY: create function tg_pslot_biu() returns opaque as '
declare
    pfrec	record;
    rename new to ps;
begin
    select into pfrec * from PField where name = ps.pfname;
    if not found then
        raise exception ''Patchfield "%" does not exist'', ps.pfname;
    end if;
    return ps;
end;
' language 'plpgsql';
QUERY: create trigger tg_pslot_biu before insert or update
    on PSlot for each row execute procedure tg_pslot_biu();
QUERY: create function tg_system_au() returns opaque as '
begin
    if new.name != old.name then
        update IFace set sysname = new.name where sysname = old.name;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_system_au after update
    on System for each row execute procedure tg_system_au();
QUERY: create function tg_iface_biu() returns opaque as '
declare
    sname	text;
    sysrec	record;
begin
    select into sysrec * from system where name = new.sysname;
    if not found then
        raise exception ''system "%" does not exist'', new.sysname;
    end if;
    sname := ''IF.'' || new.sysname;
    sname := sname || ''.'';
    sname := sname || new.ifname;
    if length(sname) > 20 then
        raise exception ''IFace slotname "%" too long (20 char max)'', sname;
    end if;
    new.slotname := sname;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_iface_biu before insert or update
    on IFace for each row execute procedure tg_iface_biu();
QUERY: create function tg_hub_a() returns opaque as '
declare
    hname	text;
    dummy	integer;
begin
    if tg_op = ''INSERT'' then
	dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
	return new;
    end if;
    if tg_op = ''UPDATE'' then
	if new.name != old.name then
	    update HSlot set hubname = new.name where hubname = old.name;
	end if;
	dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
	return new;
    end if;
    if tg_op = ''DELETE'' then
	dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
	return old;
    end if;
end;
' language 'plpgsql';
QUERY: create trigger tg_hub_a after insert or update or delete
    on Hub for each row execute procedure tg_hub_a();
QUERY: create function tg_hub_adjustslots(bpchar, integer, integer)
returns integer as '
declare
    hname	alias for $1;
    oldnslots	alias for $2;
    newnslots	alias for $3;
begin
    if newnslots = oldnslots then
        return 0;
    end if;
    if newnslots < oldnslots then
        delete from HSlot where hubname = hname and slotno > newnslots;
	return 0;
    end if;
    for i in oldnslots + 1 .. newnslots loop
        insert into HSlot (slotname, hubname, slotno, slotlink)
		values (''HS.dummy'', hname, i, '''');
    end loop;
    return 0;
end;
' language 'plpgsql';
QUERY: create function tg_hslot_biu() returns opaque as '
declare
    sname	text;
    xname	HSlot.slotname%TYPE;
    hubrec	record;
begin
    select into hubrec * from Hub where name = new.hubname;
    if not found then
        raise exception ''no manual manipulation of HSlot'';
    end if;
    if new.slotno < 1 or new.slotno > hubrec.nslots then
        raise exception ''no manual manipulation of HSlot'';
    end if;
    if tg_op = ''UPDATE'' then
	if new.hubname != old.hubname then
	    if count(*) > 0 from Hub where name = old.hubname then
		raise exception ''no manual manipulation of HSlot'';
	    end if;
	end if;
    end if;
    sname := ''HS.'' || trim(new.hubname);
    sname := sname || ''.'';
    sname := sname || new.slotno::text;
    if length(sname) > 20 then
        raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
    end if;
    new.slotname := sname;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_hslot_biu before insert or update
    on HSlot for each row execute procedure tg_hslot_biu();
QUERY: create function tg_hslot_bd() returns opaque as '
declare
    hubrec	record;
begin
    select into hubrec * from Hub where name = old.hubname;
    if not found then
        return old;
    end if;
    if old.slotno > hubrec.nslots then
        return old;
    end if;
    raise exception ''no manual manipulation of HSlot'';
end;
' language 'plpgsql';
QUERY: create trigger tg_hslot_bd before delete
    on HSlot for each row execute procedure tg_hslot_bd();
QUERY: create function tg_chkslotname() returns opaque as '
begin
    if substr(new.slotname, 1, 2) != tg_argv[0] then
        raise exception ''slotname must begin with %'', tg_argv[0];
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_chkslotname before insert
    on PSlot for each row execute procedure tg_chkslotname('PS');
QUERY: create trigger tg_chkslotname before insert
    on WSlot for each row execute procedure tg_chkslotname('WS');
QUERY: create trigger tg_chkslotname before insert
    on PLine for each row execute procedure tg_chkslotname('PL');
QUERY: create trigger tg_chkslotname before insert
    on IFace for each row execute procedure tg_chkslotname('IF');
QUERY: create trigger tg_chkslotname before insert
    on PHone for each row execute procedure tg_chkslotname('PH');
QUERY: create function tg_chkslotlink() returns opaque as '
begin
    if new.slotlink isnull then
        new.slotlink := '''';
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_chkslotlink before insert or update
    on PSlot for each row execute procedure tg_chkslotlink();
QUERY: create trigger tg_chkslotlink before insert or update
    on WSlot for each row execute procedure tg_chkslotlink();
QUERY: create trigger tg_chkslotlink before insert or update
    on IFace for each row execute procedure tg_chkslotlink();
QUERY: create trigger tg_chkslotlink before insert or update
    on HSlot for each row execute procedure tg_chkslotlink();
QUERY: create trigger tg_chkslotlink before insert or update
    on PHone for each row execute procedure tg_chkslotlink();
QUERY: create function tg_chkbacklink() returns opaque as '
begin
    if new.backlink isnull then
        new.backlink := '''';
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_chkbacklink before insert or update
    on PSlot for each row execute procedure tg_chkbacklink();
QUERY: create trigger tg_chkbacklink before insert or update
    on WSlot for each row execute procedure tg_chkbacklink();
QUERY: create trigger tg_chkbacklink before insert or update
    on PLine for each row execute procedure tg_chkbacklink();
QUERY: create function tg_pslot_bu() returns opaque as '
begin
    if new.slotname != old.slotname then
        delete from PSlot where slotname = old.slotname;
	insert into PSlot (
		    slotname,
		    pfname,
		    slotlink,
		    backlink
		) values (
		    new.slotname,
		    new.pfname,
		    new.slotlink,
		    new.backlink
		);
        return null;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_pslot_bu before update
    on PSlot for each row execute procedure tg_pslot_bu();
QUERY: create function tg_wslot_bu() returns opaque as '
begin
    if new.slotname != old.slotname then
        delete from WSlot where slotname = old.slotname;
	insert into WSlot (
		    slotname,
		    roomno,
		    slotlink,
		    backlink
		) values (
		    new.slotname,
		    new.roomno,
		    new.slotlink,
		    new.backlink
		);
        return null;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_wslot_bu before update
    on WSlot for each row execute procedure tg_Wslot_bu();
QUERY: create function tg_pline_bu() returns opaque as '
begin
    if new.slotname != old.slotname then
        delete from PLine where slotname = old.slotname;
	insert into PLine (
		    slotname,
		    phonenumber,
		    comment,
		    backlink
		) values (
		    new.slotname,
		    new.phonenumber,
		    new.comment,
		    new.backlink
		);
        return null;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_pline_bu before update
    on PLine for each row execute procedure tg_pline_bu();
QUERY: create function tg_iface_bu() returns opaque as '
begin
    if new.slotname != old.slotname then
        delete from IFace where slotname = old.slotname;
	insert into IFace (
		    slotname,
		    sysname,
		    ifname,
		    slotlink
		) values (
		    new.slotname,
		    new.sysname,
		    new.ifname,
		    new.slotlink
		);
        return null;
    end if;
    return new;
end;
' language 'plpgsql';
QUERY: create trigger tg_iface_bu before update
    on IFace for each row execute procedure tg_iface_bu();
QUERY: create function tg_hslot_bu() returns opaque as '
begin
    if new.slotname != old.slotname or new.hubname != old.hubname then
        delete from HSlot where slotname = old.slotname;
	insert into HSlot (
		    slotname,
		    hubname,
		    slotno,
		    slotlink
		) values (
		    new.slotname,
		    new.hubname,
		    new.slotno,
		    new.slotlink
		);
        return null;
    end if;
    return new;
end;