2 CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
4 delete from yearend where trans_id = old.id;
10 CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
13 CREATE FUNCTION del_department() RETURNS OPAQUE AS '
15 delete from dpt_trans where trans_id = old.id;
21 CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
23 CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
25 CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
27 CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
30 CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
32 delete from shipto where trans_id = old.id;
33 delete from customertax where customer_id = old.id;
34 delete from partscustomer where customer_id = old.id;
40 CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
43 CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
45 delete from shipto where trans_id = old.id;
46 delete from vendortax where vendor_id = old.id;
47 delete from partsvendor where vendor_id = old.id;
53 CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
56 CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
66 select into d_curr substr(curr,1,3) from defaults;
68 if TG_RELNAME = ''ar'' then
69 select into t_curr, t_transdate curr, transdate from ar where id = old.id;
71 if TG_RELNAME = ''ap'' then
72 select into t_curr, t_transdate curr, transdate from ap where id = old.id;
74 if TG_RELNAME = ''oe'' then
75 select into t_curr, t_transdate curr, transdate from oe where id = old.id;
78 if d_curr != t_curr then
80 select into t_id a.id from acc_trans ac
81 join ar a on (a.id = ac.trans_id)
83 and ac.transdate = t_transdate
85 except select a.id from ar a where a.id = old.id
89 select a.id from acc_trans ac
90 join ap a on (a.id = ac.trans_id)
92 and ac.transdate = t_transdate
94 except select a.id from ap a where a.id = old.id
100 and o.transdate = t_transdate
102 except select o.id from oe o where o.id = old.id;
105 delete from exchangerate where curr = t_curr and transdate = t_transdate;
111 ' language 'plpgsql';
114 CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
117 CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
120 CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
123 CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
127 row_data inventory%rowtype;
131 if not old.quotation then
132 for row_data in select * from inventory where oe_id = old.id loop
133 select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
135 if itemid is null then
136 delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
142 ' language 'plpgsql';
145 CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
149 CREATE FUNCTION check_department() RETURNS OPAQUE AS '
156 if new.department_id = 0 then
157 delete from dpt_trans where trans_id = new.id;
161 select into dpt_id trans_id from dpt_trans where trans_id = new.id;
164 update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
166 insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
171 ' language 'plpgsql';
174 CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
176 CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
178 CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
180 CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();