PageRenderTime 31ms CodeModel.GetById 18ms app.highlight 8ms RepoModel.GetById 2ms app.codeStats 0ms

/sql/Trigger/set_printer_id.sql

http://cupsfilter.googlecode.com/
SQL | 49 lines | 16 code | 6 blank | 27 comment | 1 complexity | fd09b267b9bfaf79797c39a6bb08dc29 MD5 | raw file
 1--CREATE PROCEDURAL LANGUAGE plpgsql;
 2
 3CREATE FUNCTION set_printer_id() RETURNS "trigger"
 4    AS $$
 5DECLARE
 6    pr_id INTEGER;    
 7begin
 8	pr_id :=(select printers.id from printers where printers.printer_name=NEW.cups_printer_name);
 9	if  (pr_id IS NULL)THEN
10		-- ? ???? ???? ??? ?????? ????????, ?? ??? ??? ?? ??????? ???? ???? ????????, ?? ??????? ? ??????? ????????? ? ?????? ID
11		INSERT INTO printers (printer_name,descr) values (NEW.cups_printer_name,'??????? ??? ???????? ?? ????? ????????? CUPS');
12		pr_id :=(select printers.id from printers where printers.printer_name=NEW.cups_printer_name);
13	END IF;
14	    
15    UPDATE reports SET printer_id=pr_id where id=NEW.id;
16    return new;
17end;
18
19$$ LANGUAGE plpgsql;
20
21CREATE TRIGGER tr_set_printer_id AFTER INSERT
22    ON reports FOR EACH ROW EXECUTE PROCEDURE set_printer_id();
23
24
25/*    
26
27CREATE FUNCTION del_product() RETURNS "trigger"
28    AS $$
29begin
30    update vendor set products=products-1 where id=old.vendor_id;
31    return old;
32end;
33$$ LANGUAGE plpgsql;
34CREATE TRIGGER tr_del_product AFTER DELETE
35    ON product FOR EACH ROW EXECUTE PROCEDURE del_product();
36
37CREATE FUNCTION upd_product() RETURNS "trigger"
38    AS $$
39begin
40    if old.vendor_id<>new.vendor_id then
41        update vendor set products=products+1 where id=new.vendor_id;
42        update vendor set products=products-1 where id=old.vendor_id;
43    end if;
44    return new
45end;
46$$ LANGUAGE plpgsql;
47CREATE TRIGGER tr_upd_product AFTER UPDATE 
48    ON product FOR EACH ROW EXECUTE PROCEDURE upd_product();
49  */