En lugar de desencadenar en SQL Server pierde SCOPE_IDENTITY?

Tengo una tabla donde creé un desencadenador INSTEAD OF para aplicar algunas reglas comerciales.

El problema es que cuando inserto datos en esta tabla, SCOPE_IDENTITY() devuelve un valor NULL , en lugar de la identidad insertada real.

Insertar código + Scope

 INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId]) VALUES ('2009-01-20', '2009-01-31', 6, 1) SELECT SCOPE_IDENTITY() 

Desencadenar:

 CREATE TRIGGER [dbo].[TR_Payments_Insert] ON [dbo].[Payment] INSTEAD OF INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF NOT EXISTS(SELECT 1 FROM dbo.Payment p INNER JOIN Inserted i ON p.CustomerId = i.CustomerId WHERE (i.DateFrom >= p.DateFrom AND i.DateFrom = p.DateFrom AND i.DateTo <= p.DateTo) ) AND NOT EXISTS (SELECT 1 FROM Inserted p INNER JOIN Inserted i ON p.CustomerId = i.CustomerId WHERE (i.DateFrom  p.DateFrom AND i.DateTo  p.DateTo) AND ((i.DateFrom >= p.DateFrom AND i.DateFrom = p.DateFrom AND i.DateTo <= p.DateTo)) ) BEGIN INSERT INTO dbo.Payment (DateFrom, DateTo, CustomerId, AdminId) SELECT DateFrom, DateTo, CustomerId, AdminId FROM Inserted END ELSE BEGIN ROLLBACK TRANSACTION END END 

El código funcionó antes de la creación de este disparador. Estoy usando LINQ to SQL en C #. No veo una forma de cambiar SCOPE_IDENTITY a @@IDENTITY . ¿Cómo hago que esto funcione?

Use @@identity lugar de scope_identity() .

Mientras scope_identity() devuelve el último ID creado en el scope actual, @@identity devuelve el último ID creado en la sesión actual.

La función scope_identity() normalmente se recomienda sobre el campo de @@identity , ya que normalmente no desea que los desencadenadores interfieran con el id, pero en este caso sí lo hace.

Como está en SQL 2008, le recomendaría usar la cláusula OUTPUT en lugar de una de las funciones de identidad personalizadas. SCOPE_IDENTITY actualmente tiene algunos problemas con consultas paralelas que me obligan a recomendarlo totalmente. @@ Identity no lo hace, pero aún no es tan explícito y tan flexible como OUTPUT. Además, OUTPUT maneja insertos de varias filas. Eche un vistazo al artículo de BOL que tiene algunos buenos ejemplos.

Tenía serias reservas sobre el uso de @@ identity, porque puede devolver la respuesta incorrecta.

Pero hay una solución para forzar que @@ identity tenga el valor de scope_identity ().

Para completar, primero voy a enumerar algunas otras soluciones para este problema que he visto en la web:

  1. Haga que el desencadenador devuelva un conjunto de filas. Luego, en un envoltorio SP que realiza la inserción, inserte INSERT Table1 EXEC sp_ExecuteSQL ... en otra tabla. Entonces scope_identity () funcionará. Esto es complicado porque requiere SQL dynamic, lo cual es un problema. Además, tenga en cuenta que SQL dynamic se ejecuta bajo los permisos del usuario que llama al SP en lugar de los permisos del propietario del SP. Si el cliente original podría insertarse en la mesa, aún debería tener ese permiso, solo sé que podrías tener problemas si niegas el permiso para insertarlo directamente en la mesa.

  2. Si hay otra clave candidata, obtenga la identidad de la (s) fila (s) insertada (s) usando esas claves. Por ejemplo, si Name tiene un índice exclusivo, puede insertar, y luego seleccionar el ID (máximo para varias filas) de la tabla que acaba de insertar usando Nombre. Si bien esto puede tener problemas de concurrencia si otra sesión elimina la fila que acaba de insertar, no es peor que en la situación original si alguien borró su fila antes de que la aplicación pudiera usarla.

Ahora, aquí le mostramos cómo hacer que su desencadenador sea seguro para @@ Identity para que devuelva el valor correcto, incluso si su SP u otro activador se insertan en una tabla con identidad después de la inserción principal .

Además, por favor incluya comentarios en su código sobre lo que está haciendo y por qué para que los futuros visitantes del activador no rompan cosas o pierdan el tiempo tratando de resolverlo.

 CREATE TRIGGER TR_MyTable_I ON MyTable INSTEAD OF INSERT AS SET NOCOUNT ON DECLARE @MyTableID int INSERT MyTable (Name, SystemUser) SELECT I.Name, System_User FROM Inserted SET @MyTableID = Scope_Identity() INSERT AuditTable (SystemUser, Notes) SELECT SystemUser, 'Added Name ' + I.Name FROM Inserted -- The following statement MUST be last in this trigger. It resets @@Identity -- to be the same as the earlier Scope_Identity() value. SELECT MyTableID INTO #Trash FROM MyTable WHERE MyTableID = @MyTableID 

Normalmente, la inserción extra en la tabla de auditoría lo rompería todo, porque dado que tiene una columna de identidad, entonces @@ Identity devolverá ese valor en lugar del de la inserción a MyTable. Sin embargo, la selección final crea un nuevo @@ valor de identidad que es el correcto, basado en el Scope_Identity () que guardamos desde antes. Esto también lo prueba contra cualquier posible activación DESPUÉS adicional en la tabla MyTable.

Actualizar:

Me acabo de dar cuenta de que un desencadenador INSTEAD OF no es necesario aquí. Esto hace todo lo que estabas buscando:

 CREATE TRIGGER dbo.TR_Payments_Insert ON dbo.Payment FOR INSERT AS SET NOCOUNT ON; IF EXISTS ( SELECT * FROM Inserted I INNER JOIN dbo.Payment P ON I.CustomerID = P.CustomerID WHERE I.DateFrom < P.DateTo AND P.DateFrom < I.DateTo ) ROLLBACK TRAN; 

Esto, por supuesto, permite que scope_identity () siga funcionando. El único inconveniente es que una inserción retrotraída en una tabla de identidad consume los valores de identidad utilizados (el valor de identidad aún se incrementa por el número de filas en el bash de inserción).

He estado mirando esto por unos minutos y no tengo certeza absoluta en este momento, pero creo que esto preserva el significado de un horario de inicio inclusivo y un tiempo de finalización exclusivo. Si la hora de finalización era inclusiva (lo que sería extraño para mí), entonces las comparaciones tendrían que usar <= en lugar de <.

Al igual que comentó araqnid, el desencadenante parece revertir la transacción cuando se cumple una condición. Puede hacerlo más fácilmente con un disparador DESPUÉS DE INSTERTAR:

 CREATE TRIGGER [dbo].[TR_Payments_Insert] ON [dbo].[Payment] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF  BEGIN ROLLBACK TRANSACTION END END 

Luego puede usar SCOPE_IDENTITY () de nuevo, porque el INSERT ya no se realiza en el desencadenador.

La condición en sí parece dejar pasar dos filas idénticas, si están en el mismo inserto. Con el disparador DESPUÉS DE INSERTAR, puede reescribir la condición como:

 IF EXISTS( SELECT * FROM dbo.Payment a LEFT JOIN dbo.Payment b ON a.Id <> b.Id AND a.CustomerId = b.CustomerId AND (a.DateFrom BETWEEN b.DateFrom AND b.DateTo OR a.DateTo BETWEEN b.DateFrom AND b.DateTo) WHERE b.Id is NOT NULL) 

Y atrapará filas duplicadas, porque ahora puede diferenciarlas según Id. También funciona si elimina una fila y la reemplaza con otra fila en la misma statement.

De todos modos, si quieres mi consejo, aléjate de los factores desencadenantes por completo. Como puede ver, incluso para este ejemplo, son muy complejos. Haga la inserción a través de un procedimiento almacenado. Son más simples y rápidos que los desencadenantes:

 create procedure dbo.InsertPayment @DateFrom datetime, @DateTo datetime, @CustomerId int, @AdminId int as BEGIN TRANSACTION IF NOT EXISTS ( SELECT * FROM dbo.Payment WHERE CustomerId = @CustomerId AND (@DateFrom BETWEEN DateFrom AND DateTo OR @DateTo BETWEEN DateFrom AND DateTo)) BEGIN INSERT into dbo.Payment (DateFrom, DateTo, CustomerId, AdminId) VALUES (@DateFrom, @DateTo, @CustomerId, @AdminId) END COMMIT TRANSACTION 

Un poco tarde para la fiesta, pero estaba investigando este tema yo mismo. Una solución alternativa es crear una tabla temporal en el procedimiento de llamada donde se realiza la inserción, insertar la identidad del ámbito en esa tabla temporal desde el interior en lugar de desencadenar, y luego leer el valor de identidad fuera de la tabla temporal una vez que se completa la inserción .

En procedimiento:

 CREATE table #temp ( id int ) ... insert statement ... select id from #temp -- (you can add sorting and top 1 selection for extra safety) drop table #temp 

En lugar de desencadenar:

 -- this check covers you for any inserts that don't want an identity value returned (and therefore don't provide a temp table) IF OBJECT_ID('tempdb..#temp') is not null begin insert into #temp(id) values (SCOPE_IDENTITY()) end 

Probablemente quieras llamarlo de otra manera que no sea #temp por el bien de la seguridad (algo largo y lo suficientemente aleatorio como para que nadie más lo use: # temp1234235234563785635).

Problema principal: el marco Trigger y Entity ambos funcionan en un scope diferente. El problema es que si genera un nuevo valor de PK en el desencadenante, se trata de un scope diferente. Por lo tanto, este comando devuelve cero filas y EF lanzará una excepción.

La solución es agregar la siguiente instrucción SELECT al final de su Trigger:

 SELECT * FROM deleted UNION ALL SELECT * FROM inserted; 

en lugar de * puede mencionar todo el nombre de columna incluyendo

 SELECT IDENT_CURRENT('tablename') AS  
    Intereting Posts