Actualizacion en Cascada
Ana Torres
cascada de tablas relacionadas en SQL, quice hacerlo con
un trigger en la tabla principal pero no me lo permite,
que debo hacer.
Gracias
Sergio León
acciones referenciales ya están definidas
Para implementar la acción referencial deberías:
1. Desactivar las restricciones FOREIGN KEY (ALTER TABLE NO CHECK), porque
éstas se comprueban antes de que se ejecute cualquier TRIGGER, pero sí
definirlas para ganar en claridad y documentar las relaciones (sp_help y
otros...)
2. Puesto que ahora la integridad referencial está completamente en tus
manos, deberías agregar un TRIGGER para UPDATE en la tabla principal
(actualización en cascada) y uno para DELETE (eliminación en cascada), pero
tambien deberías definir TRIGGERS para INSERT y UPDATE en la tabla que hace
la referencia (la tabla que "documenta" la FOREIGN KEY), puesto que tienes
que validar que cualquier fila cumpla con la restricción, es decir, que no
se agreguen ni actualicen filas con claves externas que no tenga su
correspondiente clave en la tabla principal (la que es referenciada)
Todo esto te lo cuento porque lo estoy leyendo de un libro muy majete... ;-)
De todas formas, yo una vez lo lleve a cabo y si quieres, mañana te mando el
código
"Ana Torres" <torr...@terra.com.mx> escribió en el mensaje
news:8ee201c1bf1b$77690cc0$9ee62ecf@tkmsftngxa05...
Sergio León
Aquí tienes el ejemplo:
CREATE TABLE tripulantes (
IDTripulante VARCHAR(4) NOT NULL PRIMARY KEY,
Descripcion VARCHAR(50) NOT NULL)
GO
CREATE TABLE comisiones (
IDComision INT IDENTITY NOT NULL PRIMARY KEY,
IDTripulante VARCHAR(4) NOT NULL CONSTRAINT relacion_ko REFERENCES
tripulantes (IDTripulante),
Concepto VARCHAR(50))
GO
ALTER TABLE comisiones NOCHECK CONSTRAINT relacion_ko
GO
/*
1. Desencadenador para INSERT y UPDATE sobre la tabla desde
la que se hace referencia, para no permitir ninguna inserción
o actualización si la clave externa no concuerda con ninguna
clave primaria de la tabla a la que se hace referencia
*/
create trigger INS_UPD_Comision on Comisiones
for insert,update
as
if exists
(select * from inserted where inserted.IDTripulante not in
(select IDTripulante from Tripulantes))
begin
raiserror('No se ha encontrado ningún IDTripulante correspondiente.
Se cancelará la instrucción',16,1)
rollback transaction
end
go
/*
2. Desencadenador para UPDATE sobre la tabla a la que se hace
referencia para actualizar los valores de la tabla desde la que
se hace referencia que concuerden con la clave primaria modificada.
La inserción UPDATE sobre la tabla a la que se hace referencia
sólo podrá modificar una fila por sentencia debido a la
arbitrariedad que sucede al intentar asociar las imágines anterior
y posterior de los datos en la tabla principal.
*/
create trigger UPD_Tripulantes on Tripulantes
for update
as
declare @num_affected int, @IDTripulante char(4), @old_IDTripulante
char(4)
select @num_affected=@@rowcount
if(@num_affected=0)
return
if update(IDTripulante)
begin
if (@num_affected=1)
begin
select @IDTripulante=IDTripulante from inserted
select @old_IDTripulante=IDTripulante from deleted
update Comisiones
set IDTripulante=@IDTripulante
from Comisiones
where IDTripulante=@old_IDTripulante
select @num_affected=@@rowcount
raiserror('Actualización en cascada en Tripulantes de la clave
primaria desde
%s hasta %s a %d filas de Comisiones.',10,1,
@old_IDTripulante,@IDTripulante,@num_affected)
end
else
begin
raiserror ('No es posible actualizar varios valores de clave
primaria en una
única instrucción, debido a que existe un desencadenador de
actualización
en cascada.', 16,1)
rollback transaction
end
end
go
/*
3. Desencadenador para DELETE sobre la tabla a la que se hace
referencia, para eliminar todos los registros de la tabla desde
la que se hace referencia que concuerden con la clave primaria
de la fila eliminada de la tabla a la que se hace referencia
*/
create trigger DEL_Tripulantes on Tripulantes
for delete
as
declare @counter int
delete from Comisiones where IDTripulante in
(select IDTripulante from deleted)
select @counter=@@rowcount
if (@counter > 0)
raiserror('Se han eliminado %d filas de Comisiones como resultado
de una eliminación en cascada desde la tabla Tripulantes.',
10,1,@counter)
go
/* ejemplo para comprobar la actualización y eliminación en cascada */
SELECT * FROM Tripulantes
SELECT * FROM Comisiones
INSERT INTO Tripulantes VALUES ('0001','Sergio')
INSERT INTO Tripulantes VALUES ('0002','Javier')
INSERT INTO Tripulantes VALUES ('0003','Ana')
INSERT INTO Comisiones VALUES ('0001','concepto1')
INSERT INTO Comisiones VALUES ('0002','concepto1')
INSERT INTO Comisiones VALUES ('0003','concepto1')
INSERT INTO Comisiones VALUES ('0001','concepto1')
GO
/* esta "kasca" porque no encuentra coincidencia*/
INSERT INTO Comisiones VALUES ('0004','concepto1')
GO
/* actualización en cascada */
UPDATE Tripulantes SET IDTripulante='0010' WHERE IDTripulante='0001'
/* eliminación en cascada */
DELETE Tripulantes WHERE IDTripulante='0010'
SELECT * FROM Tripulantes
SELECT * FROM Comisiones
Espero que te sirva
Un saludo