sirve - En lugar de desencadenar en SQL Server pierde SCOPE_IDENTITY?

sql server scope_identity vs @@ identity

Question

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.DateTo) OR (i.DateTo >= 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.DateTo) OR (i.DateTo >= 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?

Answer 1

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 <Condition>
    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 declaración.

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

Answer 2

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.

Answer 3

Problema principal: el marco Trigger y Entity ambos funcionan en un alcance diferente. El problema es que si genera un nuevo valor de PK en el desencadenante, se trata de un alcance 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 <IdentityColumnname>

Answer 4

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 dinámico, lo cual es un problema. Además, tenga en cuenta que SQL dinámico 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 intento 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 <.

Answer 5

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

Answer 6

Use @@identity lugar de scope_identity() .

Mientras scope_identity() devuelve el último ID creado en el alcance 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.