-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy paths-06-ilap-cah-s2-servicio-laptop-trigger.sql
160 lines (142 loc) · 6.43 KB
/
s-06-ilap-cah-s2-servicio-laptop-trigger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
--@Autor: Carlos A. Hernández A. & Marco A. Moreno Guerra
--@Fecha creación: 16/05/2020
--@Descripción: Definición del trigger para operaciones DML para SERVICIO_LAPTOP
-- en CAHABDD_S2
create or replace trigger servicio_laptop_cah_s2
instead of insert or update or delete on SERVICIO_LAPTOP
declare
v_count number;
begin
case
when inserting then
select count(1)
into v_count
from LAPTOP_F5
where laptop_id = :new.laptop_id;
-- Inserción local (en CAHABDD_S2)
if v_count > 0 then
insert into SERVICIO_LAPTOP_F4(num_servicio, laptop_id, importe,
diagnostico, factura, sucursal_id)
values (:new.num_servicio, :new.laptop_id, :new.importe,
:new.diagnostico, :new.factura, :new.sucursal_id);
else
select count(1)
into v_count
from LAPTOP_F3
where laptop_id = :new.laptop_id;
-- Inserción remota en MAMGBD_S2
if v_count > 0 then
insert into TI_SERVICIO_LAPTOP_F2(num_servicio, laptop_id,
importe, diagnostico, factura, sucursal_id)
values (:new.num_servicio, :new.laptop_id, :new.importe,
:new.diagnostico, :new.factura, :new.sucursal_id);
insert into SERVICIO_LAPTOP_F2
select *
from TI_SERVICIO_LAPTOP_F2
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
delete from TI_SERVICIO_LAPTOP_F2
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
else
select count(1)
into v_count
from LAPTOP_F4
where laptop_id = :new.laptop_id;
-- Inserción remota en MAMGBD_S1
if v_count > 0 then
insert into TI_SERVICIO_LAPTOP_F3(num_servicio, laptop_id,
importe, diagnostico, factura, sucursal_id)
values (:new.num_servicio, :new.laptop_id, :new.importe,
:new.diagnostico, :new.factura, :new.sucursal_id);
insert into SERVICIO_LAPTOP_F3
select *
from TI_SERVICIO_LAPTOP_F3
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
delete from TI_SERVICIO_LAPTOP_F3
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
else
select count(1)
into v_count
from LAPTOP_F2
where laptop_id = :new.laptop_id;
-- Inserción remota en CAHABDD_S1
if v_count > 0 then
insert into TI_SERVICIO_LAPTOP_F1(num_servicio, laptop_id,
importe, diagnostico, factura, sucursal_id)
values (:new.num_servicio, :new.laptop_id, :new.importe,
:new.diagnostico, :new.factura, :new.sucursal_id);
insert into SERVICIO_LAPTOP_F1
select *
from TI_SERVICIO_LAPTOP_F1
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
delete from TI_SERVICIO_LAPTOP_F1
where num_servicio = :new.num_servicio
and laptop_id = :new.laptop_id;
else
raise_application_error(-20020, 'Error de integridad'
|| ' para el campo LAPTOP_ID: '
|| :new.sucursal_id
|| ' No se encontró el registro padre en los fragmentos');
end if;
end if;
end if;
end if;
when deleting then
select count(1)
into v_count
from LAPTOP_F5
where laptop_id = :old.laptop_id;
-- Delete local (en CAHABDD_S2)
if v_count > 0 then
delete from SERVICIO_LAPTOP_F4
where num_servicio = :old.num_servicio
and laptop_id = :old.laptop_id;
else
select count(1)
into v_count
from LAPTOP_F3
where laptop_id = :old.laptop_id;
-- Delete remoto en MAMGBD_S2
if v_count > 0 then
delete from SERVICIO_LAPTOP_F2
where num_servicio = :old.num_servicio
and laptop_id = :old.laptop_id;
else
select count(1)
into v_count
from LAPTOP_F4
where laptop_id = :old.laptop_id;
-- Delete remoto en MAMGBD_S1
if v_count > 0 then
delete from SERVICIO_LAPTOP_F3
where num_servicio = :old.num_servicio
and laptop_id = :old.laptop_id;
else
select count(1)
into v_count
from LAPTOP_F2
where laptop_id = :old.laptop_id;
-- Delete remoto en CAHABDD_S1
if v_count > 0 then
delete from SERVICIO_LAPTOP_F1
where num_servicio = :old.num_servicio
and laptop_id = :old.laptop_id;
else
raise_application_error(-20020, 'Error de integridad'
|| ' para el campo LAPTOP_ID: '
|| :new.sucursal_id
|| ' No se encontró el registro padre en los fragmentos');
end if;
end if;
end if;
end if;
when updating then
raise_application_error(-20030, 'Las operaciones UPDATE no tienen soporte aun.');
end case;
end;
/
show errors