· 4 years ago · Jul 22, 2021, 08:54 PM
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_doc_type;
9
10-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11
12CREATE TABLE tb_bond_title_doc_type (
13 id_contract INTEGER NOT NULL,
14 cd_document_type VARCHAR NOT NULL,
15 ds_document_type VARCHAR NOT NULL,
16 CONSTRAINT tb_bond_title_doc_type_pk PRIMARY KEY (id_contract, cd_document_type)
17);
18
19INSERT INTO tb_bond_title_doc_type (id_contract, cd_document_type, ds_document_type ) VALUES
20(39, 'DIE', 'Documento de Informações Essenciais (DIE)'),
21(39, 'CONTRACT_ADHESION', 'Termo de Adesão'),
22(39, 'ADVERTISING_MATERIAL', 'Material Publicitário'),
23(39, 'OTHER', 'Outros');
24
25-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
26
27CREATE TABLE tb_bond_title_document_log (
28 id_contract int4 NULL,
29 id_bond int4 NULL,
30 id_document int4 NULL,
31 id_user int4 NULL,
32 required_doc boolean not null,
33 seq_doc int4 not null,
34 ds_document varchar NULL,
35 url_document varchar NULL,
36 ip varchar NULL,
37 user_agent varchar NULL,
38 dh_log timestamp DEFAULT CURRENT_TIMESTAMP,
39 cd_document_type varchar NULL,
40 cd_document_status varchar NULL,
41 "action" varchar NULL
42);
43
44
45-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
46
47CREATE TABLE tb_bond_title_document (
48 id_contract int4 NOT NULL,
49 id_bond int4 NOT NULL,
50 id_document bigserial NOT NULL,
51 id_user int4 NULL,
52 required_doc boolean not null,
53 seq_doc int4 not null,
54 ds_document varchar NULL,
55 url_document varchar NULL,
56 ip varchar NULL,
57 user_agent varchar NULL,
58 cd_document_type varchar NULL,
59 cd_document_status varchar NULL,
60 CONSTRAINT tb_bond_title_document_pk PRIMARY KEY (id_contract, id_bond, id_document),
61 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),
62 CONSTRAINT tb_bond_title_tb_bond_title_document_fk FOREIGN KEY (id_contract, id_bond) REFERENCES tb_bond_title(id_contract, id_bond)
63);
64
65-- Function para gerar logs, criada antes da Trigger abaixo que executa ela
66
67CREATE OR REPLACE FUNCTION fun_tb_bond_title_document_log()
68 RETURNS trigger
69 LANGUAGE plpgsql
70AS $function$
71 BEGIN
72 INSERT INTO PUBLIC.tb_bond_title_document_log (
73 id_contract,
74 id_bond,
75 id_document,
76 id_user,
77 required_doc,
78 seq_doc,
79 ds_document,
80 url_document,
81 ip,
82 user_agent,
83 cd_document_type,
84 cd_document_status,
85 action
86 ) VALUES (
87 NEW.id_contract,
88 NEW.id_bond,
89 NEW.id_document,
90 NEW.id_user,
91 NEW.required_doc,
92 NEW.seq_doc,
93 NEW.ds_document,
94 NEW.url_document,
95 NEW.ip,
96 NEW.user_agent,
97 NEW.cd_document_type,
98 NEW.cd_document_status,
99 TG_OP
100 );
101 RETURN NEW;
102 RETURN NULL;
103 END;
104$function$;
105
106-- Trigger para gerar os logs de documentos inseridos, atualizados ou inativos
107
108create trigger trigger_tb_bond_title_document_log after
109insert
110 or
111update
112 on
113 tb_bond_title_document for each row execute procedure fun_tb_bond_title_document_log();
114
115
116-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
117
118COMMIT;