none
Error al crear un SP "La tabla de destino de la instrucción INSERT no puede estar en ninguno de los dos lados de una relación (clave primaria, clave externa)" RRS feed

  • Pregunta

  • Buen día.

    Estoy tratando de hacer un SP que actualiza un registro y del update obtener el número de auditoria del campo iDBAudId, para luego hacer un insert en la tabla auditoria junto con otros datos

    Este es el código:

    Create Procedure [Odontologia].[PrestacionTermina_spU] @iUsuarioId Int, @iOdtDId Int, @cNota As nVarchar(500)
    As
    SET NOCOUNT ON;
    Begin Try
    	Begin Transaction
    		Insert Into Odontologia.DBAuditoria
    			  (iDBAudId, iUsuarioId, iAudTipoId, cCambio)
    		Select t.iDBAudId, @iUsuarioId, 2, 'Terminar Prestación'
    		From 
    		   (
    			Update Odontologia.OdontogramaD
    			 Set iTerminado = 1, 
    				 dTerminado = GetDate(), 
    				 iUserTerminadoId = @iUsuarioId
    			   OutPut inserted.iDBAudId 
    			  Where iOdtDId = @iOdtDId
    			) t;
    
    		If isnull(@cNota, '') <> ''
    		   begin
    			Insert Into Odontologia.OdontogramaDNota 
    			       (iOdtDId, cNota, iUsuarioId)
    			Values (@iOdtDId, @cNota, @iUsuarioId);
    		   end
    		Commit Transaction
    	End Try

    Mensaje de error:

    Mens 356, Nivel 16, Estado 1, Procedimiento PrestacionTermina_spU, Línea 6
    La tabla de destino 'Odontologia.DBAuditoria' de la instrucción INSERT no puede estar en ninguno de los dos lados de una relación (clave primaria, clave externa) cuando la cláusula FROM contiene una instrucción anidada INSERT, UPDATE, DELETE o MERGE. Se encontró una restricción de referencia 'FK_DBAuditoria_AuditoriaTipo'.

    La restricción 'FK_DBAuditoria_AuditoriaTipo' es un relación de la tabla App.AuditoriaTipo con Odontologia.DBAuditoria mediante los campos iAudTipoId para mentener la integridad referencial.

    No entiendo el mensaje de error, pueden explicarme a que se debe.

    Me gustaría resolverlo de esta manera sin tener que utilizar una variable tipo tabla en donde almacenar la salida del update y luego hacer el insert

    Saludos.

    Mauricio

    miércoles, 25 de junio de 2014 16:05

Respuestas

  • Victor,

    Desde la version 2008 podemos usar lo que se conoce como "composable DML", lo cual permite que se pueda insertar la salida de otra sentencia de manejo de data.


    AMB

    Some guidelines for posting questions...

    miércoles, 25 de junio de 2014 18:31
  • Exactamente lo que dice. La tabla donde estas insertando no puede formar parte de alguna restriccion de clave foranrea ya sea como la tabla que referencia (hijo) o la referenciada (padre).

    Ejemplo:

    --use Northwind;
    --go
    create table dbo.T1 (
    c1 int not null identity(1, 1) primary key,
    c2 varchar(25) null
    );
    create table dbo.T2 (
    c1 int not null primary key,
    c2 varchar(25) null
    --, foreign key (c1) references dbo.T1(c1)
    );
    insert into dbo.T1 (c2)
    values ('uno'), (null);
    select * from dbo.T1;
    select * from dbo.T2;
    insert into dbo.T2 (c1)
    select c1 
    from 
    	(
    	update dbo.T1 
    	set c2 = 'dos'
    	output deleted.c1, deleted.c2 
    	where c1 = 2
    	) as T;
    select * from dbo.T1;
    select * from dbo.T2;
    GO
    drop table dbo.T2, dbo.T1;
    go

    Si corres el ejemplo veras que no hay error y los datos que se actualizaron en tabla T1 fueron insertados en la tabla T2. Si ahora quitas el comentario en la tabla T2 relacionado con la restriccion de clave foranea y ejecutas el script entonces reciviras el mismo error.


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta mpulla jueves, 26 de junio de 2014 15:37
    miércoles, 25 de junio de 2014 18:28

Todas las respuestas

  • Hola, desde la más absoluta ignorancia, no se si el lenguaje SQL te permite insertar datos de manera masiva con una SELECT que conteniene un UPDATE...

    Quizá funcione si haces primero la operación de INSERT y después el UPDATE.

    Saludos!!


    Víctor del Valle - http://www.vrdelvalle.net

    miércoles, 25 de junio de 2014 17:17
  • ¿Por qué no pruebas a dirigir el OUTPUT del UPDATE directamente sobre la tabla de auditoría?

    Sería más o menos así. No puedo comprobarlo sin tener las tablas base:

    CREATE PROCEDURE [Odontologia].[PrestacionTermina_spU] @iUsuarioId Int, @iOdtDId Int, @cNota As nVarchar(500)
    AS
    SET NOCOUNT ON;
    BEGIN TRY
    	BEGIN TRANSACTION
    
    	UPDATE Odontologia.OdontogramaD
    	SET 
    		iTerminado = 1, 
    		dTerminado = GetDate(), 
    		iUserTerminadoId = @iUsuarioId
    	OUTPUT 
    		inserted.iDBAudId, @iUsuarioId, 2, 'Terminar Prestación'
    	INTO 
    		Odontologia.DBAuditoria (iDBAudId, iUsuarioId, iAudTipoId, cCambio)
    	WHERE 
    		iOdtDId = @iOdtDId
    
    	IF isnull(@cNota, '') <> ''
    	BEGIN
    		INSERT INTO Odontologia.OdontogramaDNota 
    			   (iOdtDId, cNota, iUsuarioId)
    		VALUES (@iOdtDId, @cNota, @iUsuarioId);
    	END
    	COMMIT TRANSACTION
    END TRY


    El mensaje de error te está diciendo que SQL Server tiene una limitación con respecto a instrucciones de este tipo:

    INSERT INTO Tabla
    SELECT FROM
    (
       UPDATE ....
    )

    Y esa limitación es que la tabla "Tabla" no puede tener claves externas, ni tampoco puede ser referenciada por una clave externa de otra tabla.

    La verdad es que la sintaxis es muy rara, tengo que reconocer que nunca la he visto ni usado. Generalmente las sentencias UPDATE no devuelven filas, pero un UPDATE con OUTPUT sí que lo puede hacer, por lo tanto tiene cierto sentido.


    EntityLite: A Lightweight, Database First, Micro ORM

    miércoles, 25 de junio de 2014 18:14
  • Me temo que tampoco va a funcionar. Acabo de revisar la documentación y dice que la tabla de destino del OUTPUT no puede:

    output_table cannot:
    • Have enabled triggers defined on it.

    • Participate on either side of a FOREIGN KEY constraint.

    • Have CHECK constraints or enabled rules.

    Así que vas a tener que enviar la salida del OUTPUT a una tabla temporal o una variable de tabla y luego insertarlo en la tabla de auditoría:

    CREATE PROCEDURE [Odontologia].[PrestacionTermina_spU] @iUsuarioId Int, @iOdtDId Int, @cNota As nVarchar(500)
    AS
    SET NOCOUNT ON;
    BEGIN TRY
    	BEGIN TRANSACTION
    	
    	DECLARE @t TABLE(iDBAudId int)
    
    	UPDATE Odontologia.OdontogramaD
    	SET 
    		iTerminado = 1, 
    		dTerminado = GetDate(), 
    		iUserTerminadoId = @iUsuarioId
    	OUTPUT 
    		inserted.iDBAudId
    	INTO 
    		@t(iDBAudId)
    	WHERE 
    		iOdtDId = @iOdtDId
    		
    	INSERT INTO Odontologia.DBAuditoria (iDBAudId, iUsuarioId, iAudTipoId, cCambio)
    	SELECT iDBAudId, @iUsuarioId, 2, 'Terminar Prestación'
    	FROM @t
    	
    
    	IF isnull(@cNota, '') <> ''
    	BEGIN
    		INSERT INTO Odontologia.OdontogramaDNota 
    			   (iOdtDId, cNota, iUsuarioId)
    		VALUES (@iOdtDId, @cNota, @iUsuarioId);
    	END
    	COMMIT TRANSACTION
    END TRY


    EntityLite: A Lightweight, Database First, Micro ORM

    miércoles, 25 de junio de 2014 18:25
  • Exactamente lo que dice. La tabla donde estas insertando no puede formar parte de alguna restriccion de clave foranrea ya sea como la tabla que referencia (hijo) o la referenciada (padre).

    Ejemplo:

    --use Northwind;
    --go
    create table dbo.T1 (
    c1 int not null identity(1, 1) primary key,
    c2 varchar(25) null
    );
    create table dbo.T2 (
    c1 int not null primary key,
    c2 varchar(25) null
    --, foreign key (c1) references dbo.T1(c1)
    );
    insert into dbo.T1 (c2)
    values ('uno'), (null);
    select * from dbo.T1;
    select * from dbo.T2;
    insert into dbo.T2 (c1)
    select c1 
    from 
    	(
    	update dbo.T1 
    	set c2 = 'dos'
    	output deleted.c1, deleted.c2 
    	where c1 = 2
    	) as T;
    select * from dbo.T1;
    select * from dbo.T2;
    GO
    drop table dbo.T2, dbo.T1;
    go

    Si corres el ejemplo veras que no hay error y los datos que se actualizaron en tabla T1 fueron insertados en la tabla T2. Si ahora quitas el comentario en la tabla T2 relacionado con la restriccion de clave foranea y ejecutas el script entonces reciviras el mismo error.


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta mpulla jueves, 26 de junio de 2014 15:37
    miércoles, 25 de junio de 2014 18:28
  • Victor,

    Desde la version 2008 podemos usar lo que se conoce como "composable DML", lo cual permite que se pueda insertar la salida de otra sentencia de manejo de data.


    AMB

    Some guidelines for posting questions...

    miércoles, 25 de junio de 2014 18:31
  • Hola Hunchback, no había visto ni trabajado nunca el "composable DML". Me lo apunto.

    Gracias y un saludo!!


    Víctor del Valle - http://www.vrdelvalle.net

    jueves, 26 de junio de 2014 6:54