none
Ayuda T-SQL, Mantener integridad referencial con trigger RRS feed

  • Pregunta

  • Hola, buen día, estoy haciendo un disparador (trigger) con T-SQL (SQL Server 2017) para mantener la integridad referencial entre dos tablas, debido a que ambas tablas tienen un cliclo en sus claves externas, por lo que he decidido hacer con trigger el equivalente a la integridad referencial a la hora de insertar, modificar y eliminar los datos de una tabla. las tablas son las siguientes:

    create table Alojamiento(
    nombre varchar(30) not null primary key,
    direccion varchar(40) not null,
    telefono varchar(20) not null,
    cant_habit int,
    contacto int
    );

    create table Personal(
    codigo int identity(1,1) not null primary key,
    nombre varchar(30) not null,
    direccion varchar(40) not null,
    nif varchar(10) unique not null check (nif like '[0-9][a-z][A-Z]'),
    nombre_alojam varchar(30) not null
    );

    alter table Alojamiento
    add foreign key(contacto) references Personal(codigo)
    on delete set null
    on update cascade;


    El trigger que estoy realizando es el siguiente:

    create TRIGGER ModificarEnCascada ON Alojamiento
    for UPDATE
    AS
    BEGIN
    update Personal
    set nombre_alojam=(select nombre from inserted)
    where nombre_alojam=(select nombre from deleted)
    END
    GO

    Sucede que cuando intento modificar el campo 'nombre' en la tabla Alojamiento, con el objetivo de que se dispare el trigger y me modifique todas las filas en la tabla personal, me sale el siguiente error:

    -----------------------------------

    No se actualizó ninguna fila

    Los datos de la fila 1 no se confirmaron.
    Origen del error: .Net SQL Client Data Provider.
    Mensaje de error: La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión.
    Se terminó la instrucción.

    -----------------------------------

    He intentado modificar el código de la siguiente manera:

    create TRIGGER ModificarEnCascada ON Alojamiento
    for UPDATE
    AS
    BEGIN
    declare @i varchar(30);
    declare @d varchar(30);

    select @i=nombre from inserted;
    select @d=nombre from deleted;

    update Personal
    set nombre_alojam=@i;
    where nombre_alojam=@d;
    END
    GO


    Con el mismo mensaje de error. Alguien me puede ayudar, no logro encontrar el error.

    Saludos y buen día a todos

    William
    jueves, 1 de octubre de 2020 12:29

Todas las respuestas

  • Creo que lo que intentas hacer es esto:

    update Personal
    set nombre_alojam = (select top 1 nombre FROM inserted)
    where nombre_alojam=(select top 1  nombre from deleted)

    Ten en cuenta que si haces modificaciones de multiples registros no te funcionrá bien.


    jueves, 1 de octubre de 2020 15:17
  • Hola william_cuba1981:

    Eso que intentas hacer, no esta bien.

    Si quieres seguir por ese camino, deberías de tener una clave subrogada para Alojamiento, de manera que no intervenga en el negocio y así puedas mantener la integridad.

    Por ejemplo

    create table Alojamiento(
    id int primary key,
    nombre varchar(30) not null,
    direccion varchar(40) not null,
    telefono varchar(20) not null,
    cant_habit int,
    contacto int
    );
    go
    create table Personal(
    codigo int identity(1,1) not null primary key,
    nombre varchar(30) not null,
    direccion varchar(40) not null,
    nif varchar(10) unique not null check (nif like '[0-9][a-z][A-Z]'),
    id_alojamiento int not null
    );
    alter table Alojamiento
    add foreign key(contacto) references Personal(codigo)
    on delete set null
    on update cascade;
    go
    Insert into Personal (nombre, direccion, nif, id_alojamiento)
    values
    ('Ana','dir Ana','1aB',1),
    ('Bea','dir Bea','2bC',1),
    ('Eva','dir Eva','3dE',2);
    Insert into Alojamiento (id,nombre, direccion, telefono, cant_habit, contacto)
    values
    (1,'Alojamiento 1', 'dir Aloj 1','1234',2,1),
    (2,'Alojamiento 2', 'dir Aloj 2','4451',1,3);
    go

    Ahora el trigger no te es necesario porque el id del alojamiento, no le importa a nadie más que al código, no tiene porque ser visible ni tan siquiera modificable. 

    He puesto un int, pero podría ser un uniqueidentifier, o cualquier tipo de clave.

    Pero la ventaja de hacerlo de este modo, es que en el trigger, si puedes relacionar registros para utilizar operaciones de conjuntos y no tener que limitarte a una regla que te dará problemas.

    Create or Alter TRIGGER ModificarEnCascada ON Alojamiento
    for UPDATE
    AS
    BEGIN
    
    	update P
    		set id_alojamiento= i.id
    		From Personal p 
    		inner join deleted d on p.id_alojamiento = d.id
    		left join inserted i on d.id = i.id 
    
    END
    GO

    Si observas el trigger, se relacionan las pseudotablas inserted o deleted por su clave primaria y de ellas, puedes hacer lo que quieras en una sentencia.

    Por otro lado, ¿estás seguro de que el check del nif cumple con lo que quieres?

    check (nif like '[0-9][a-z][A-Z]')

    Esto te obliga a poner 1 dígito numérico, y un par de letras. Total 3 caracteres

    Si aún tienes que mantener la idea inicial, se me ocurre, sin darte garantías de que esto funcione, podrías capturar en el trigger, dentro de una variable tipo Tabla o 2 mejor dicho, lo que contenga inserted, lo que contenga deleted, y por el orden de ellas, establecer una relación entre ambas. Pero todo esto son conjeturas, no probadas.

    Trigger after update

    https://javifer2.wordpress.com/2020/04/15/trigger-que-es-como-se-hace-y-como-funciona-after-for-update/

    Variables de tabla

    https://javifer2.wordpress.com/2019/11/16/variables-de-tabla/

    Numerar registros

    https://javifer2.wordpress.com/2019/11/11/row-number-numerar-filas/

    viernes, 2 de octubre de 2020 4:06
  • Hola william_cuba1981:

    En cuanto a lo del check. 

    Puedes aplicar una función escalar al check, de manera que sea la función la que compruebe el nif.

    Te pongo un ejemplo

    Create or alter function dbo.isValidNif (@nif varchar(10))
    returns bit
    As
    Begin
    return
    -- Algoritmo válido para calcular el nif
    -- http://www.interior.gob.es/web/servicios-al-ciudadano/dni/calculo-del-digito-de-control-del-nif-nie
    	(Select 
    		case when isnumeric(left(@nif,len(@nif)-1))=1 
    		And RIGHT(@nif,1) like '[a-z]' and len(@nif)>7
    	then 1 else 0 end as isValid)
    End
    go

    Tienes en la función creada el enlace a la página oficial, donde te explica como se coge la parte numérica del nif y se divide entre 23, y la letra tiene que corresponder con una de las asignadas en la tabla.

    Yo simplemente he puesto que los primeros caracteres, sean números (ojo que para la función isNumeric, hay algunos valores que también son válidos como el símbolo de moneda $ por ejemplo), y que el último carácter sea una letra, y que tenga más de 7 caracteres.

    Luego su implementación es:

    create table PersonalConNif
    (
    codigo int identity(1,1) not null primary key,
    nombre varchar(30) not null,
    direccion varchar(40) not null,
    nif varchar(10) unique not null check (dbo.isValidNif(nif)=1),
    id_alojamiento int not null
    );

    el campo, invoca a la función escalar, y si esta retorna 1, es válido.

    Espero te ayude.

    viernes, 2 de octubre de 2020 5:06