manera de hacer esto en sql server

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

¿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.

Answer 4

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,  '[e-mail]',0,'20190501'),
	(1,119,'20190311','REF2','ISA','PEREZ',		12,'[e-mail]',0,'20190801'),
	(1,117,'20190321','REF3','ANA','SIL',		15,'[e-mail]',0,'20190801'),
	(2,241,'20190421','REF4','CARLOS','ESTEBAN',17,'[e-mail]',0,'20190801'),
	(2,263,'20190217','REF5','SILVIA','JIMENEZ',1, '[e-mail]',0,'20190801'),
	(2,299,'20190115','REF6','ANA','RODERA',	3, '[e-mail]',0,'20190801'),
	(3,312,'20190512','REF7','FELIPE','SOBRINO',6, '[e-mail]',0,'20190801'),
	(3,315,'20190621','REF8','ANA','GIL',		12,'[e-mail]',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.