Clause OUTPUT (Transact-SQL)

Retourne des informations ou des expressions basées sur toutes les lignes affectées par une instruction INSERT, UPDATE, DELETE ou MERGE. Ces résultats peuvent être retournés à l'application en cours de traitement afin d'être utilisés notamment avec des messages de confirmation, des opérations d'archivage et d'autres spécifications d'application similaires. Il est également possible d'insérer ces résultats dans une table ou dans une variable de table. En outre, vous pouvez capturer les résultats d'une clause OUTPUT dans une instruction imbriquée INSERT, UPDATE, DELETE ou MERGE et les insérer dans une table ou une vue cible.

[!REMARQUE]

Une instruction UPDATE, INSERT ou DELETE qui a une clause OUTPUT retourne des lignes au client même si l'instruction rencontre des erreurs et est annulée. Le résultat ne doit pas être utilisé si des erreurs se produisent lorsque vous exécutez l'instruction.

Utilisés dans :

DELETE

INSERT

UPDATE

MERGE

Conventions de la syntaxe Transact-SQL

Syntaxe

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

Arguments

• @table\_variable
  Spécifie une variable de table dans laquelle les lignes retournées sont insérées au lieu d'être retournées à l'appelant. @table\_variable doit être déclarée avant les instructions INSERT, UPDATE, DELETE et MERGE.

  Si column_list n'est pas spécifiée, la variable table doit contenir le même nombre de colonnes que le jeu de résultats OUTPUT. Les exceptions sont des colonnes calculées et des colonnes d'identité qui doivent être ignorées. Si column_list est spécifiée, toutes les colonnes ignorées doivent autoriser des valeurs NULL ou comporter des valeurs par défaut leur étant affectées.

  Pour plus d'informations sur les variables de table, consultez table (Transact-SQL).

• output_table
  Spécifie une table dans laquelle les lignes retournées sont insérées au lieu d'être retournées à l'appelant. output_table peut être une table temporaire.

  Si column_list n'est pas spécifiée, la table doit contenir le même nombre de colonnes que le jeu de résultats OUTPUT. Les exceptions sont des colonnes calculées et des colonnes d'identité. Elles doivent être ignorées. Si column_list est spécifiée, toutes les colonnes ignorées doivent autoriser des valeurs NULL ou comporter des valeurs par défaut leur étant affectées.

  L'argument output_table ne peut pas :

  • Avoir de déclencheurs activés.

  • Participer aux côtés d'une contrainte FOREIGN KEY.

  • Avoir des contraintes CHECK ou des règles activées.

• column_list
  Liste facultative de noms de colonne sur la table cible de la clause INTO. Elle est analogue à la liste de colonnes autorisée dans l'instruction INSERT.

• scalar_expression
  Combinaison de symboles et d'opérateurs qui a pour résultat une seule valeur. Les fonctions d'agrégation ne sont pas autorisées dans scalar_expression.

  Toute référence aux colonnes dans la table en cours de modification doit être qualifiée avec le préfixe INSERTED ou DELETED.

• column_alias_identifier
  Autre nom utilisé pour faire référence au nom de colonne.

• DELETED
  Préfixe de colonne qui spécifie la valeur supprimée par les opérations de mise à jour ou de suppression. Les colonnes dont le préfixe est DELETED reflètent la valeur avant la fin de l'instruction UPDATE, DELETE ou MERGE.

  Le préfixe DELETED ne peut pas être utilisé avec la clause OUTPUT dans l'instruction INSERT.

• INSERTED
  Préfixe de colonne qui spécifie la valeur ajoutée par les opérations d'insertion ou de mise à jour. Les colonnes dont le préfixe est INSERTED reflètent la valeur après la fin de l'instruction UPDATE, INSERT ou MERGE mais avant l'exécution des déclencheurs.

  Le préfixe INSERTED ne peut pas être utilisé avec la clause OUTPUT dans l'instruction DELETE.

• from_table_name
  Préfixe de colonne qui spécifie une table incluse dans la clause FROM d'une instruction DELETE, UPDATE ou MERGE qui est utilisée pour spécifier les lignes à mettre à jour ou à supprimer.

  Si la table en cours de modification est également spécifiée dans la clause FROM, toutes les références aux colonnes de cette table doivent être qualifiées avec le préfixe INSERTED ou DELETED.

• *
  Spécifie que toutes les colonnes affectées par les opérations de suppression, d'insertion ou de mise à jour sont retournées dans leur ordre d'apparition dans la table.

  Par exemple, dans l'instruction DELETE qui suit, OUTPUT DELETED.* retourne toutes les colonnes supprimées de la table ShoppingCartItem :

  DELETE Sales.ShoppingCartItem
      OUTPUT DELETED.*;
  

• column_name
  Référence de colonne explicite. Toutes les références à la table en cours de modification doivent être qualifiées correctement par le préfixe INSERTED ou DELETED, le cas échéant. Exemple : INSERTED**.**column_name.

• $action
  Disponible uniquement pour l'instruction MERGE. Spécifie une colonne de type nvarchar(10) dans la clause OUTPUT d'une instruction MERGE qui retourne l'une des trois valeurs suivantes pour chaque ligne : 'INSERT', 'UPDATE' ou 'DELETE', en fonction de l'action effectuée sur cette ligne.

Notes

Les clauses OUTPUT <dml_select_list> et OUTPUT <dml_select_list> INTO { **@**table_variable | output_table } peuvent être définies dans une seule instruction INSERT, UPDATE, DELETE ou MERGE.

[!REMARQUE]

Sauf indication contraire, les références à la clause OUTPUT sont relatives aux clauses OUTPUT et OUTPUT INTO.

La clause OUTPUT est utile pour extraire la valeur d'identité ou les colonnes calculées après une opération INSERT ou UPDATE

Lorsqu'une colonne calculée est comprise dans <dml_select_list>, la colonne correspondante de la table sortante ou la variable de table n'est pas une colonne calculée. Les valeurs de la nouvelle colonne sont les valeurs calculées lors de l'exécution de l'instruction.

Vous n'avez aucune garantie que l'ordre d'application des modifications dans la table et l'ordre d'insertion des lignes dans la table de sortie ou la variable de table correspondent.

Si les paramètres ou les variables sont modifiés dans le cadre d'une instruction UPDATE, la clause OUTPUT retourne toujours la valeur du paramètre ou de la variable, telle qu'elle était avant l'exécution de l'instruction et non après sa modification.

Vous pouvez utiliser l'instruction OUTPUT avec l'instruction UPDATE ou DELETE placée sur un curseur utilisant la syntaxe WHERE CURRENT OF.

La clause OUTPUT n'est pas prise en charge dans les instructions suivantes :

• Instructions DML faisant référence aux vues partitionnées locales, aux vues partitionnées distribuées ou aux tables distantes.

• Instructions INSERT contenant une instruction EXECUTE.

• Les prédicats de texte intégral ne sont pas autorisés dans la clause OUTPUT lorsque le niveau de compatibilité de la base de données a la valeur 100.

• La clause OUTPUT INTO ne peut pas être utilisée pour insérer une vue ou une fonction d'ensemble de lignes.

• Une fonction définie par l'utilisateur ne peut pas être créée si elle contient une clause OUTPUT INTO avec une table comme cible.

Pour empêcher tout comportement non déterministe, la clause OUTPUT ne peut pas contenir les références suivantes :

• Les sous-requêtes ou les fonctions définies par l'utilisateur qui permettent d'accéder à des données utilisateur ou système, ou qui sont supposées permettre d'y accéder. Les fonctions définies par l'utilisateur sont supposées permettre d'accéder à des données si elles ne sont pas liées au schéma.

• Une colonne d'une vue ou fonction de table inline lorsque cette colonne est définie par l'une des méthodes suivantes :

  • Une sous-requête.

  • Une fonction définie par l'utilisateur qui offre un accès à des données utilisateur ou système, ou qui est supposée permettre d'y accéder.

  • Une colonne calculée qui contient une fonction définie par l'utilisateur qui offre un accès aux données utilisateur ou système dans sa définition.

  Lorsque SQL Server détecte une telle colonne dans la clause OUTPUT, l'erreur 4186 est générée. Pour plus d'informations, consultez MSSQLSERVER_4186.

Insertion de données retournées à partir d'une clause OUTPUT dans une table

Lorsque vous capturez les résultats d'une clause OUTPUT dans une instruction INSERT, UPDATE, DELETE ou MERGE imbriquée et insérez ces résultats dans une table cible, n'oubliez pas les informations suivantes :

• L'opération entière est atomique. Soit l'instruction INSERT et l'instruction DML imbriquée qui contient la clause OUTPUT s'exécutent toutes les deux, soit l'instruction entière échoue.

• Les restrictions suivantes s'appliquent à la cible de l'instruction INSERT externe :

  • La cible ne peut pas être une expression de table commune, une vue ou une table distante.

  • La cible ne peut pas avoir de contrainte FOREIGN KEY ou être référencée par une contrainte FOREIGN KEY.

  • Des déclencheurs ne peuvent pas être définis sur la cible.

  • La cible ne peut pas participer à la réplication de fusion ou à des abonnements pouvant être mis à jour pour la réplication transactionnelle.

• Les restrictions suivantes s'appliquent à l'instruction DML imbriquée :

  • La cible ne peut pas être une table distante ou une vue partitionnée.

  • La source elle-même ne peut pas contenir de clause <dml_table_source>.

• La clause OUTPUT INTO n'est pas prise en charge dans les instructions INSERT qui contiennent une clause <dml_table_source>.

• @@ROWCOUNT retourne les lignes insérées uniquement par l'instruction INSERT externe.

• @@IDENTITY, SCOPE_IDENTITY et IDENT_CURRENT retournent les valeurs d'identité générées uniquement par l'instruction DML imbriquée, et non celles générées par l'instruction INSERT externe.

• Les notifications de requête traitent l'instruction comme une entité unique, et le type de tout message créé sera le type de l'instruction DML imbriquée, même si la modification significative provient de l'instruction INSERT externe elle-même.

• Dans la clause <dml_table_source>, les clauses SELECT et WHERE ne peuvent pas inclure de sous-requêtes, de fonctions d'agrégation, de fonctions de classement, de prédicats de texte intégral, de fonctions définies par l'utilisateur qui effectuent l'accès aux données ou la fonction TEXTPTR.

Déclencheurs

Les colonnes retournées depuis l'instruction OUTPUT illustrent les données, telles qu'elles sont après la fin de l'instruction INSERT, UPDATE ou DELETE mais avant l'exécution des déclencheurs.

Pour les déclencheurs INSTEAD OF, les résultats retournés sont produits, comme si l'instruction INSERT, UPDATE ou DELETE avait eu lieu, même en cas d'absence de modifications suite à l'opération des déclencheurs. Si une instruction comprenant une clause OUTPUT est utilisée à l'intérieur du corps d'un déclencheur, les alias de table doivent être utilisés pour faire référence aux tables inserted et deleted du déclencheur, afin d'éviter des références de colonne en double avec les tables INSERTED et DELETED associées à OUTPUT.

Si la clause OUTPUT est spécifiée sans mot clé INTO, la cible de l'opération DML ne peut pas comporter de déclencheur activé pour l'action DML en question. Par exemple, si la clause OUTPUT est définie dans une instruction UPDATE, la table cible ne peut pas comporter de déclencheurs UPDATE activés.

Si l'option sp_configure disallow results from triggers est définie, une clause OUTPUT sans clause INTO provoque l'échec de l'instruction lors de son appel à partir d'un déclencheur.

Types de données

La clause OUTPUT prend en charge les types de données d'objets volumineux : nvarchar(max), varchar(max), varbinary(max), text, ntext, image et xml. Si vous utilisez la clause .WRITE dans l'instruction UPDATE pour modifier une colonne nvarchar(max), varchar(max) ou varbinary(max), les images complètes avant et après des valeurs sont retournées si elles sont référencées. La fonction TEXTPTR( ) ne peut pas s'afficher comme partie intégrante d'une expression sur une colonne text, ntext ou image de la clause OUTPUT.

Files d'attente

Vous pouvez utiliser OUTPUT dans les applications utilisant les tables comme files d'attente ou garder les jeux de résultats intermédiaires. En d'autres termes, l'application ajoute ou supprime constamment des lignes de la table. L'exemple suivant utilise la clause OUTPUT dans une instruction DELETE afin de retourner la ligne supprimée à l'application appelante.

USE AdventureWorks2012;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

Cet exemple supprime une ligne d'une table utilisée comme file d'attente et retourne les valeurs supprimées à l'application de traitement en une seule fois. D'autres sémantiques peuvent également être mises en place, notamment l'utilisation d'une table pour implémenter une pile. Cependant, SQL Server ne garantit pas l'ordre de traitement et de retour des lignes par les instructions DML à l'aide de la clause OUTPUT. C'est à l'application d'inclure une clause WHERE adéquate pour obtenir les sémantiques souhaitées ou de comprendre qu'aucun ordre n'est garanti lorsque plusieurs lignes sont qualifiées pour l'opération DML. L'exemple suivant utilise une sous-requête et suppose que le caractère unique est une caractéristique de la colonne DatabaseLogID, afin de mettre en place les sémantiques de classement souhaitées.

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
)
go

INSERT INTO dbo.table1 VALUES 
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete' 
SELECT * FROM dbo.table1;

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'
SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--@MyTableVar, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

[!REMARQUE]

Utilisez l'indicateur de table READPAST dans les instructions UPDATE et DELETE si votre scénario permet à plusieurs applications d'exécuter une lecture destructive à partir d'une table. Ce cas de figure empêche les problèmes de verrouillage qui peuvent survenir si une autre application est déjà entrain de lire le premier enregistrement dans la table.

Autorisations

Les autorisations SELECT sont obligatoires sur toutes les colonnes extraites par le biais de <dml_select_list> ou utilisées dans <scalar_expression>.

Les autorisations INSERT sont obligatoires sur toutes les tables spécifiées dans <output_table>.

Exemples

A.Utilisation de OUTPUT INTO avec une instruction INSERT simple

L'exemple suivant insère une ligne dans la table ScrapReason et utilise la clause OUTPUT pour retourner les résultats de l'instruction à la variable @MyTableVar table. Étant donné que la colonne ScrapReasonID est définie avec une propriété IDENTITY, aucune valeur n'est spécifiée dans l'instruction INSERT pour cette colonne. Cependant, notez que la valeur générée par le moteur de base de données pour cette colonne est retournée dans la clause OUTPUT de la colonne inserted.ScrapReasonID.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

B.Utilisation de OUTPUT avec une instruction DELETE

L'exemple suivant supprime toutes les lignes de la table ShoppingCartItem. La clause OUTPUT deleted.* spécifie que les résultats de l'instruction DELETE, à savoir toutes les colonnes dans les lignes supprimées, sont retournés vers l'application appelante. L'instruction SELECT suivante vérifie les résultats de l'opération de suppression dans la table ShoppingCartItem.

USE AdventureWorks2012;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

C.Utilisation de OUTPUT INTO avec une instruction UPDATE

L'exemple suivant met à jour la colonne VacationHours à hauteur de 25 % pour 10 premières lignes aléatoires dans la table Employee. La clause OUTPUT retourne la valeur de VacationHours qui existe avant l'application de l'instruction UPDATE dans la colonne deleted.VacationHours et la valeur mise à jour dans la colonne inserted.VacationHours à la variable @MyTableVar table.

Deux instructions SELECT suivent ; elles retournent les valeurs dans @MyTableVar, ainsi que les résultats de la mise à jour dans la table Employee.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D.Utilisation de OUTPUT INTO pour retourner une expression

L'exemple suivant reprend l'exemple C en définissant une expression dans la clause OUTPUT comme étant la différence entre la valeur mise à jour VacationHours et la valeur VacationHours avant sa mise à jour. La valeur de cette expression est retournée à la variable table @MyTableVar dans la colonne VacationHoursDifference.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    VacationHoursDifference int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.VacationHours - deleted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, 
    VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

E.Utilisation de OUTPUT INTO avec from_table_name dans une instruction UPDATE

L'exemple suivant met à jour la colonne ScrapReasonID dans la table WorkOrder pour toutes les commandes avec ProductID et ScrapReasonID. La clause OUTPUT INTO retourne les valeurs depuis la table en cours de mise à jour (WorkOrder) ainsi que de la table Product. La table Product est utilisée dans la clause FROM pour spécifier les lignes à mettre à jour. Étant donné qu'un déclencheur AFTER UPDATE est défini sur la table WorkOrder, le mot clé INTO est obligatoire.

USE AdventureWorks2012;
GO
DECLARE @MyTestVar table (
    OldScrapReasonID int NOT NULL, 
    NewScrapReasonID int NOT NULL, 
    WorkOrderID int NOT NULL,
    ProductID int NOT NULL,
    ProductName nvarchar(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT deleted.ScrapReasonID,
       inserted.ScrapReasonID, 
       inserted.WorkOrderID,
       inserted.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p 
    ON wo.ProductID = p.ProductID 
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID, 
    ProductID, ProductName 
FROM @MyTestVar;
GO

F.Utilisation de OUTPUT INTO avec from_table_name dans une instruction DELETE

L'exemple suivant supprime les lignes dans la table ProductProductPhoto en fonction des critères de recherche définis dans la clause FROM de l'instruction DELETE. La clause OUTPUT retourne les colonnes de la table en cours de suppression (deleted.ProductID, deleted.ProductPhotoID) ainsi que les colonnes de la table Product. Cette table est utilisée dans la clause FROM pour spécifier les lignes à supprimer.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

G.Utilisation de OUTPUT INTO avec un type de données d'objets volumineux

L'exemple suivant met à jour une valeur partielle dans DocumentSummary, une colonne nvarchar(max) de la table Production.Document, à l'aide de la clause .WRITE. Le terme components est remplacé par le terme features, en spécifiant le terme de remplacement, l'emplacement de départ (décalage) du terme à remplacer dans les données existantes et le nombre de caractères à remplacer (longueur). L'exemple utilise la clause OUTPUT pour retourner les images avant et après de la colonne DocumentSummary à la variable @MyTableVar table. Notez que les images complètes avant et après de la colonne DocumentSummary sont retournées.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table (
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
SELECT SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

H.Utilisation de OUTPUT dans un déclencheur INSTEAD OF

L'exemple suivant utilise la clause OUTPUT dans un déclencheur pour retourner les résultats de l'opération de celui-ci. Tout d'abord, une vue est créée sur la table ScrapReason, puis un déclencheur INSTEAD OF INSERT est défini sur la vue qui ne permet qu'à la colonne Name de la table de base d'être modifiée par l'utilisateur. Étant donné que la colonne ScrapReasonID est une colonne IDENTITY de la table de base, le déclencheur ignore la valeur fournie par l'utilisateur. Ceci permet au moteur de base de données de générer automatiquement la bonne valeur. De plus, la valeur fournie par l'utilisateur pour ModifiedDate est ignorée et définie sur la date actuelle. La clause OUTPUT retourne les valeurs insérées dans la table ScrapReason.

USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL
    DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS (SELECT ScrapReasonID, Name, ModifiedDate
    FROM Production.ScrapReason);
GO
CREATE TRIGGER dbo.io_ScrapReason 
    ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted 
--because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (Name, ModifiedDate)
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, 
               INSERTED.ModifiedDate
    SELECT Name, getdate()
    FROM inserted;
END
GO
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
VALUES (99, N'My scrap reason','20030404');
GO

Voici le jeu de résultats généré le 12 avril 2004 ('2004-04-12'). Notez que les colonnes ScrapReasonIDActual et ModifiedDate illustrent les valeurs générées par l'opération du déclencheur plutôt que les valeurs fournies dans l'instruction INSERT.

ScrapReasonID  Name             ModifiedDate

-------------  ---------------- -----------------------

17             My scrap reason  2004-04-12 16:23:33.050

I.Utilisation de OUTPUT INTO avec des colonnes d'identité et des colonnes calculées

L'exemple suivant crée la table EmployeeSales, puis y insère plusieurs lignes à l'aide d'une instruction INSERT, avec une instruction SELECT pour récupérer les données des tables sources. La table EmployeeSales contient une colonne d'identité (EmployeeID) et une colonne calculée (ProjectedSales). Étant donné que ces valeurs sont générées par le moteur de base de données SQL Server lors de l'insertion, aucune de ces colonnes ne peut être définie dans @MyTableVar.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

J.Utilisation de OUTPUT et OUTPUT INTO dans une instruction unique

L'exemple suivant supprime les lignes dans la table ProductProductPhoto en fonction des critères de recherche définis dans la clause FROM de l'instruction DELETE. La clause OUTPUT INTO retourne les colonnes de la table en cours de suppression (deleted.ProductID, deleted.ProductPhotoID), ainsi que les colonnes de la table Product à la variable table @MyTableVar. La table Product est utilisée dans la clause FROM pour spécifier les lignes à supprimer. La clause OUTPUT retourne à l'application appelante deleted.ProductID, les colonnes deleted.ProductPhotoID, la date et l'heure de suppression de la ligne de la table ProductProductPhoto.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate 
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
WHERE p.ProductID BETWEEN 800 and 810;

--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID 
FROM @MyTableVar;
GO

K.Insertion de données retournées à partir d'une clause OUTPUT

L'exemple suivant capture les données retournées par la clause OUTPUT d'une instruction MERGE et insère ces données dans une autre table. L'instruction MERGE met quotidiennement à jour la colonne Quantity de la table ProductInventory, en fonction des commandes traitées dans la table SalesOrderDetail. Elle supprime également les lignes correspondant aux produits dont le stock passe à 0 ou moins. Cet exemple capture les lignes supprimées et les insère dans une autre table, ZeroInventory, qui effectue le suivi des produits en rupture de stock.

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20070401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory; 

Voir aussi

Référence

DELETE (Transact-SQL)

INSERT (Transact-SQL)

UPDATE (Transact-SQL)

table (Transact-SQL)

CREATE TRIGGER (Transact-SQL)

sp_configure (Transact-SQL)