RgbnF2cT

1BEGIN;
2
3-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4
5DROP TRIGGER IF EXISTS trigger_tb_bond_title_document_log on tb_bond_title_document;
6DROP TABLE IF EXISTS tb_bond_title_document;
7DROP TABLE IF EXISTS tb_bond_title_document_log;
8DROP TABLE IF EXISTS tb_bond_title_document_status;
9DROP TABLE IF EXISTS tb_bond_title_doc_type;
10
11-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12
13CREATE TABLE tb_bond_title_doc_type (
14    id_contract INTEGER NOT NULL,
15    cd_document_type VARCHAR NOT NULL,
16    ds_document_type VARCHAR NOT NULL,
17    CONSTRAINT tb_bond_title_doc_type_pk PRIMARY KEY (id_contract, cd_document_type)
18);
19
20INSERT INTO tb_bond_title_doc_type (id_contract, cd_document_type, ds_document_type ) VALUES
21(39, 'DIE', 'Documento de Informações Essenciais (DIE)'),
22(39, 'CONTRACT_ADHESION', 'Termo de Adesão'),
23(39, 'ADVERTISING_MATERIAL', 'Material Publicitário'),
24(39, 'OTHER', 'Outros');
25
26
27-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
28
29CREATE TABLE tb_bond_title_document_status (
30	id_contract int4 NOT NULL,
31	cd_document_status varchar NOT NULL,
32	ds_document_status varchar NULL,
33	CONSTRAINT tb_bond_title_document_status_pk PRIMARY KEY (id_contract, cd_document_status)
34);
35
36INSERT INTO tb_bond_title_document_status(id_contract, cd_document_status, ds_document_status) VALUES
37(39, 'AT', 'Ativo'),
38(39, 'IN', 'Inativo');
39
40
41-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42
43CREATE TABLE tb_bond_title_document_log (
44	id_contract int4 NULL,
45	id_bond int4 NULL,
46	id_document int4 NULL,
47	id_user int4 NULL,
48    required_doc boolean not null,
49	ds_document varchar NULL,
50	url_document varchar NULL,
51	ip varchar NULL,
52	user_agent varchar NULL,
53	dh_last_update timestamp NULL,
54	cd_document_type varchar NULL,
55	cd_document_status varchar NULL,
56	"action" varchar NULL
57);
58
59
60-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
61
62CREATE TABLE tb_bond_title_document (
63	id_contract int4 NOT NULL,
64	id_bond int4 NOT NULL,
65	id_document int4 NOT NULL,
66	id_user int4 NULL,
67    required_doc boolean not null,
68	ds_document varchar NULL,
69	url_document varchar NULL,
70	ip varchar NULL,
71	user_agent varchar NULL,
72	dh_last_update timestamp NULL,
73	cd_document_type varchar NULL,
74	cd_document_status varchar NULL,
75	CONSTRAINT tb_bond_title_document_pk PRIMARY KEY (id_contract, id_bond, id_document),
76	CONSTRAINT tb_bond_title_document_status_tb_bond_title_document_fk FOREIGN KEY (id_contract,cd_document_status) REFERENCES public.dm_coe_document_status(id_contract,cd_document_status),
77	CONSTRAINT tb_bond_title_doc_type_tb_bond_title_document_fk FOREIGN KEY (id_contract,cd_document_type) REFERENCES tb_bond_title_doc_type(id_contract,cd_document_type),
78	CONSTRAINT tb_bond_title_tb_bond_title_document_fk FOREIGN KEY (id_contract, id_bond) REFERENCES tb_bond_title(id_contract, id_bond)
79);
80
81-- Function para gerar logs, criada antes da Trigger abaixo que executa ela
82
83CREATE OR REPLACE FUNCTION fun_tb_bond_title_document_log()
84 RETURNS trigger
85 LANGUAGE plpgsql
86AS $function$
87    BEGIN
88        INSERT INTO PUBLIC.tb_bond_title_document_log (
89            id_contract,
90            id_bond,
91            id_document,
92            id_user,
93            required_doc,
94            ds_document,
95            url_document,
96            ip,
97            user_agent,
98            dh_last_update,
99            cd_document_type,
100            cd_document_status,
101            action
102        ) VALUES (
103            NEW.id_contract,
104            NEW.id_bond,
105            NEW.id_document,
106            NEW.id_user,
107            NEW.required_doc,
108            NEW.ds_document,
109            NEW.url_document,
110            NEW.ip,
111            NEW.user_agent,
112            NEW.dh_last_update,
113            NEW.cd_document_type,
114            NEW.cd_document_status,
115            TG_OP
116        );
117        RETURN NEW;
118    RETURN NULL;
119    END;
120$function$;
121
122-- Trigger para gerar os logs de documentos inseridos, atualizados ou inativos
123
124create trigger trigger_tb_bond_title_document_log after
125insert
126    or
127update
128    on
129    tb_bond_title_document for each row execute procedure fun_tb_bond_title_document_log();
130
131
132-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
133
134COMMIT;