none
manera de hacer esto en sql server RRS feed

  • Pregunta

  • Hola:

    Tengo dos tablas

    CREATE TABLE [dbo].[eventos publico](
    	[ep0] [int] IDENTITY(1,1) NOT NULL,
    	[ep1] [int] NOT NULL,
    	[nombre] [nvarchar](25) NOT NULL,
    	[finicio] [date] NOT NULL,
    	[ffin] [date] NOT NULL,
    	[importe] [money] NOT NULL,
    	[envigor] [bit] NOT NULL,
    	[descripcion] [nvarchar](max) NOT NULL,
    

    y

    CREATE TABLE [dbo].[solicitudes invitaciones](
    	[si0] [int] IDENTITY(1,1) NOT NULL,
    	[si1] [int] NOT NULL,
    	[si2] [int] NOT NULL,
    	[fecha] [smalldatetime] NOT NULL,
    	[referencia] [nvarchar](15) NOT NULL,
    	[nombre] [nvarchar](25) NOT NULL,
    	[apellidos] [nvarchar](50) NOT NULL,
    	[region] [int] NOT NULL,
    	[correo] [nvarchar](80) NOT NULL,
    	[estado] [tinyint] NOT NULL,
    	[fbaja] [date] NULL,
    

    Las dos están relacionadas ep0=si1

    Quisiera que antes de dar de baja eventos publico compruebe si hay solicitudes invitaciones con la clave ep0, la de ese evento. Si es así, que no lo dé de baja y en su lugar ponga 0 a envigor. Si no es así, que lo dé de baja.

    Es decir, que no dé de baja ningún evento (eventos publico) si hay solicitud de invitación a ese evento (solicitudes invitaciones). Así no pierdo la relación y tengo el evento para futuras consultas, sólo que el evento está obsoleto -envigor=0-

    ¿Se puede hacer? ¿Cómo?

    Creí que con un trigger valía. Pero no.

    Muchísimas gracias por la ayuda.

    viernes, 1 de noviembre de 2019 16:42

Respuestas

  • Sí, con un Trigger se puede hacer.

    Tienes que crear un trigger del tipo "INSTEAD OF DELETE" sobre la tabla de eventos. Dentro del trigger haces un Select sobre la tabla de solicitudes a ver si existe alguna para cada evento recibido en la pseudo-tabla "deleted", y segun el resultado cambias envigor o haces el delete.

    Eso es correcto y funciona, pero es un poco más dificil de programar de lo que parece, porque el trigger se podría disparar una sola vez para múltiples borrados realizados simultáneamente, por lo que "deleted" podría contener más de un registro, y tendrías que buscar por cada uno de ellos si existen solicitudes. Es factible, pero te costará un buen rato escribir y depurar el código del trigger.

    • Propuesto como respuesta Pablo RubioModerator viernes, 1 de noviembre de 2019 21:45
    • Marcado como respuesta volar.2016 sábado, 2 de noviembre de 2019 10:34
    viernes, 1 de noviembre de 2019 16:55
  • ¿Está bien? No pareció tan difícil.


    No, no está bien. Solo funcionará si únicamente viene un solo registro dentro de DELETED. Si te llegan varios registros, solo se procesará el último.

    Por ejemplo, si ejecutas DELETE FROM [dbo].[eventos publico] WHERE Nombre like 'Juan%' y en la tabla existen varios registros en los que el nombre empieza por Juan, entonces con ese trigger solo se borrará el último de todos esos registros.
    viernes, 1 de noviembre de 2019 19:15

Todas las respuestas

  • Sí, con un Trigger se puede hacer.

    Tienes que crear un trigger del tipo "INSTEAD OF DELETE" sobre la tabla de eventos. Dentro del trigger haces un Select sobre la tabla de solicitudes a ver si existe alguna para cada evento recibido en la pseudo-tabla "deleted", y segun el resultado cambias envigor o haces el delete.

    Eso es correcto y funciona, pero es un poco más dificil de programar de lo que parece, porque el trigger se podría disparar una sola vez para múltiples borrados realizados simultáneamente, por lo que "deleted" podría contener más de un registro, y tendrías que buscar por cada uno de ellos si existen solicitudes. Es factible, pero te costará un buen rato escribir y depurar el código del trigger.

    • Propuesto como respuesta Pablo RubioModerator viernes, 1 de noviembre de 2019 21:45
    • Marcado como respuesta volar.2016 sábado, 2 de noviembre de 2019 10:34
    viernes, 1 de noviembre de 2019 16:55
  • Hola:

    Tengo

    alter TRIGGER borrar_eventos_publico
           ON [dbo].[eventos publico]
    INSTEAD OF DELETE
    AS
    BEGIN
           SET NOCOUNT ON;
           DECLARE @id0 INT
    	   SELECT @id0 = DELETED.ep0        
           FROM DELETED
           IF exists(select si1 from [solicitudes invitaciones] where si1=@id0) update dbo.[eventos publico] set envigor=0 where ep0=@id0
    	   else delete from dbo.[eventos publico] where ep0=@id0
    END

    ¿Está bien? No pareció tan difícil.

    Muchas gracias.

    PD: ¿Dónde se ven los triggers? ¿En procedimientos almacenados? En 'desencadenadores de bases de datos' no los veo.

    Uso sql server 2017.

    No me aparecen los triggers cuando doy botón derecho encima de desencadenadores.

    Me he creado otro trigger instead of sobre la misma tabla.

    Me sale este error

    Mensaje 2111, nivel 16, estado 1, procedimiento borrar_eventos_publico, línea 1 [línea de inicio de lote 0]
    No se puede crear el desencadenador 'borrar_eventos_publico' en tabla 'dbo.eventos publico' porque en este objeto ya existe un desencadenador INSTEAD OF, DELETE.

    O sea, el trigger está pero no lo veo

    ¿Me podéis ayudar?

    Tal vez los trigger instead of los mete en otra parte

    Por otra parte estoy leyendo un artículo

    https://desarrolloweb.com/articulos/triggers-sustitucion-sistema.html

    donde se dice

    Solo podemos utilizar estos triggers si están asociados a vistas, además actúan siempre a nivel de fila.

    ¿Es cierto? Tal vez se ha actualizado sql server para permitir usar tablas.

    Y tengo for each row, con lo que me imagino que puedo borrar si hay varios registros haciendo uso de old.ep0 ¿Puede ser?

    Mchas gracias.

    PD: veré tu ejemplo de trigger update.

    Ya encontré los triggers.

    Leo un mensaje

    https://stackoverflow.com/questions/34198396/instead-of-delete-trigger-code-for-multiple-rows

    donde se plantea una posible solución al borrado de varios registros, mediante una join.

    ¿pero no seguirá sirviendo para un solo registro?

    Me gustaría saber vuestra opinión, y guiarme.

    Muchas gracias.

    PD: creo que el after update no me funcionaría pues no se trata de borrar después de una actualización.

    Las tablas tienen millones de registros







    • Editado volar.2016 sábado, 2 de noviembre de 2019 12:07
    viernes, 1 de noviembre de 2019 18:40
  • ¿Está bien? No pareció tan difícil.


    No, no está bien. Solo funcionará si únicamente viene un solo registro dentro de DELETED. Si te llegan varios registros, solo se procesará el último.

    Por ejemplo, si ejecutas DELETE FROM [dbo].[eventos publico] WHERE Nombre like 'Juan%' y en la tabla existen varios registros en los que el nombre empieza por Juan, entonces con ese trigger solo se borrará el último de todos esos registros.
    viernes, 1 de noviembre de 2019 19:15
  • Hola Volar.2016:

    Puedes hacerlo de un modo no muy complejo así:

    CREATE TABLE [dbo].[eventos publico](
    	[ep0] [int] IDENTITY(1,1) NOT NULL,
    	[ep1] [int] NOT NULL,
    	[nombre] [nvarchar](25) NOT NULL,
    	[finicio] [date] NOT NULL,
    	[ffin] [date] NOT NULL,
    	[importe] [money] NOT NULL,
    	[envigor] [bit] NOT NULL,
    	[descripcion] [nvarchar](max) NOT NULL
    	)
    	GO
    	CREATE TABLE [dbo].[solicitudes invitaciones](
    	[si0] [int] IDENTITY(1,1) NOT NULL,
    	[si1] [int] NOT NULL,
    	[si2] [int] NOT NULL,
    	[fecha] [smalldatetime] NOT NULL,
    	[referencia] [nvarchar](15) NOT NULL,
    	[nombre] [nvarchar](25) NOT NULL,
    	[apellidos] [nvarchar](50) NOT NULL,
    	[region] [int] NOT NULL,
    	[correo] [nvarchar](80) NOT NULL,
    	[estado] [tinyint] NOT NULL,
    	[fbaja] [date] NULL
    	)
    	GO
    
    CREATE TRIGGER tr_cambioEstado_eventos_publico
           ON [dbo].[eventos publico]
    AFTER UPDATE
    AS
    BEGIN
           SET NOCOUNT ON;
    	   SET XACT_ABORT ON;
      		/* Creas una variable de tabla para almacenar los ids, que tiene inserted*/
    		Declare @table table (ep0 int, ep1 int, sinInvitas bit);
    		INSERT INTO @table
    		SELECT I.ep0, I.ep1, 
    				case when s.si0 IS null then 1 else 0 end as conInvitas 
    				FROM inserted I 
    					LEFT JOIN dbo.[solicitudes invitaciones] s on I.ep1 = s.si1;
    
    /* Los que tienen alguna invitacion se updatean */
    		update e set envigor = 1
    			FROM dbo.[eventos publico] e INNER JOIN @table T ON e.ep0 = T.ep0
    				where T.sinInvitas = 0;
    /* Los que no la tienen se borran */
    		
    		DELETE FROM dbo.[eventos publico] where ep0 in (select ep0 from @table t where sinInvitas=1);
    
    END
    GO
    INSERT INTO [eventos publico](
    	[ep1],
    	[nombre],
    	[finicio],
    	[ffin],
    	[importe],
    	[envigor],
    	[descripcion])
    	VALUES
    	(1, 'A', '20190101','20190801',15,0,'EVENTO1'),
    	(2, 'B', '20190101','20190801',12,0,'EVENTO2'),
    	(3, 'C', '20190101','20190801',14,0,'EVENTO3'),
    	(4, 'D', '20190101','20190801',15,0,'EVENTO3');
    GO
    INSERT INTO [solicitudes invitaciones]
    (
    	[si1],
    	[si2],
    	[fecha],
    	[referencia],
    	[nombre],
    	[apellidos],
    	[region],
    	[correo],
    	[estado],
    	[fbaja]
    	)
    	VALUES
    	(1,189,'20190201','REF1','ANA','CASTELAO',	1,  'sucorreo@hotmail.com',0,'20190501'),
    	(1,119,'20190311','REF2','ISA','PEREZ',		12,'sucorreo2@hotmail.com',0,'20190801'),
    	(1,117,'20190321','REF3','ANA','SIL',		15,'sucorreo3@hotmail.com',0,'20190801'),
    	(2,241,'20190421','REF4','CARLOS','ESTEBAN',17,'sucorreo4@hotmail.com',0,'20190801'),
    	(2,263,'20190217','REF5','SILVIA','JIMENEZ',1, 'sucorreo5@hotmail.com',0,'20190801'),
    	(2,299,'20190115','REF6','ANA','RODERA',	3, 'sucorreo6@hotmail.com',0,'20190801'),
    	(3,312,'20190512','REF7','FELIPE','SOBRINO',6, 'sucorreo8@hotmail.com',0,'20190801'),
    	(3,315,'20190621','REF8','ANA','GIL',		12,'sucorreo7@hotmail.com',0,'20190801');
    GO
    UPDATE D SET envigor=1 FROM dbo.[eventos publico] D WHERE ep0 in (3,4)
    go
    select * from dbo.[solicitudes invitaciones]
    SELECT * FROM dbo.[eventos publico]
    

    Si observas el código, en el trigger de update de la tabla, se utiliza una variable de tabla para almacenar los ids, que se relacionan con [solicitudes invitaciones], de esta manera en el puedes realizar diferentes sentencias. La update para el campo en vigor, y el delete para la tabla solicitudes.

    PD: ¿Dónde se ven los triggers? ¿En procedimientos almacenados? En 'desencadenadores de bases de datos' no los veo.

    Una vez creados en el Sql Server Management Studio, tienes que actualizar (botón derecho sobre la carpeta desencadenadores), porque los objetos no se refrescan automaticamente.

    sábado, 2 de noviembre de 2019 5:51