Created
October 22, 2014 18:22
-
-
Save matheusoliveira/05b5edc579124116cde0 to your computer and use it in GitHub Desktop.
Trigger para atualização de número em tabela filha
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE venda(venda_id serial primary key, ...); | |
CREATE TABLE venda_item(venda_id integer references venda(venda_id), num_item integer, ...); | |
CREATE OR REPLACE FUNCTION tg_item_venda_sequencia() | |
RETURNS trigger | |
LANGUAGE plpgsql AS | |
$$ | |
BEGIN | |
-- Bloqueia acesso concorrente! | |
PERFORM 1 FROM venda v WHERE v.venda_id = NEW.venda_id FOR UPDATE; | |
NEW.num_item := 1 + (SELECT COALESCE(max(i.num_item),0) FROM venda_item i WHERE i.venda_id = NEW.venda_id); | |
RETURN NEW; | |
END; | |
$$; | |
CREATE TRIGGER tg_item_venda_sequencia | |
BEFORE INSERT ON venda_item | |
FOR EACH ROW EXECUTE PROCEDURE tg_item_venda_sequencia(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment