MS SQL Server
  1. MS SQL Server
  3. la création de triggersurs pour After Insert, After Update et After Delete dans SQL
Intereting Posts
TSQL – Groupes et dates des îles Est-il possible d'écrire une vue de database qui englobe les relations un-à-plusieurs? select sans sql (applique externe) Service Broker ne démarre pas Dossiers de procédure stockés SQL Server / regroupement SQL select distinct par 2 colonnes ou plus Problème de syntaxe OPENQUERY SQL Server 2012 Comment réorganiser une table SQL comment get la sum de cette colonne dans le server sql Serveur SQL nettoyage des bases de données, journaux de transactions? Base de données d'images SQL Server Sélectionnez le numéro de version et le nom de database de toutes les bases de données Comment les jointures interne et externe sont-elles implémentées dans SQL Server? Déclencheur SQL Server avec message d'erreur Comment puis-je faire une boucle sur les colonnes par nom dans un composant de script SSIS?

la création de triggersurs pour After Insert, After Update et After Delete dans SQL

J'essaie de créer un sortinggger chaque fois que des données sont insérées, mises à jour ou supprimées. Le triggersur pour l'insertion fonctionne bien mais j'ai quelques problèmes avec la mise à jour et la suppression. Voici le triggersur Après insertion: 

 CREATE TRIGGER trgAfterInsert ON [DR].[dbo].[Derived_Values] FOR INSERT AS insert into [Main].[dbo].[Derived_Values_Test] (BusinessUnit,Questions, Answer) SELECT BusinessUnit,Questions, Answer FROM inserted;

C'est ce que j'ai fait pour la mise à jour mais ça semble faux, 

 CREATE TRIGGER trgAfterUpdate ON [DR].[dbo].[Derived_Values] FOR UPDATE AS Update [Main].[dbo].[Derived_Values_Test] set BusinessUnit = 'Updated Record -- After Update Trigger.'; GO

Comment créer les triggersurs de mise à jour et de suppression? Merci.

( Mise à jour: négligé une faute en la matière, j'ai corrigé)

( Update2: j'ai écrit de memory le code foiré, réparé)

( Update3: vérifier sur SQLFiddle ) 

 create table Derived_Values ( BusinessUnit nvarchar(100) not null ,Questions nvarchar(100) not null ,Answer nvarchar(100) ) go ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values PRIMARY KEY CLUSTERED (BusinessUnit, Questions); create table Derived_Values_Test ( BusinessUnit nvarchar(150) ,Questions nvarchar(100) ,Answer nvarchar(100) ) go CREATE TRIGGER trgAfterUpdate ON [Derived_Values] FOR UPDATE AS begin declare @BusinessUnit nvarchar(50) set @BusinessUnit = 'Updated Record -- After Update Trigger.' insert into [Derived_Values_Test] --(BusinessUnit,Questions, Answer) SELECT @BusinessUnit + i.BusinessUnit, i.Questions, i.Answer FROM inserted i inner join deleted d on i.BusinessUnit = d.BusinessUnit end go CREATE TRIGGER trgAfterDelete ON [Derived_Values] FOR UPDATE AS begin declare @BusinessUnit nvarchar(50) set @BusinessUnit = 'Deleted Record -- After Delete Trigger.' insert into [Derived_Values_Test] --(BusinessUnit,Questions, Answer) SELECT @BusinessUnit + d.BusinessUnit, d.Questions, d.Answer FROM deleted d end go insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22') UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11'); UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12'); UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21'); UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22'); delete Derived_Values;

et alors: 

 SELECT * FROM Derived_Values; go select * from Derived_Values_Test; Record Count: 0; BUSINESSUNIT QUESTIONS ANSWER Updated Record -- After Update Trigger.BU1 Q11 Updated Answers A11 Deleted Record -- After Delete Trigger.BU1 Q11 A11 Updated Record -- After Update Trigger.BU1 Q12 Updated Answers A12 Deleted Record -- After Delete Trigger.BU1 Q12 A12 Updated Record -- After Update Trigger.BU2 Q21 Updated Answers A21 Deleted Record -- After Delete Trigger.BU2 Q21 A21 Updated Record -- After Update Trigger.BU2 Q22 Updated Answers A22 Deleted Record -- After Delete Trigger.BU2 Q22 A22

( Update4: Si vous voulez synchroniser: SQLFiddle ) 

 create table Derived_Values ( BusinessUnit nvarchar(100) not null ,Questions nvarchar(100) not null ,Answer nvarchar(100) ) go ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values PRIMARY KEY CLUSTERED (BusinessUnit, Questions); create table Derived_Values_Test ( BusinessUnit nvarchar(150) not null ,Questions nvarchar(100) not null ,Answer nvarchar(100) ) go ALTER TABLE Derived_Values_Test ADD CONSTRAINT PK_Derived_Values_Test PRIMARY KEY CLUSTERED (BusinessUnit, Questions); CREATE TRIGGER trgAfterInsert ON [Derived_Values] FOR INSERT AS begin insert [Derived_Values_Test] (BusinessUnit,Questions,Answer) SELECT i.BusinessUnit, i.Questions, i.Answer FROM inserted i end go CREATE TRIGGER trgAfterUpdate ON [Derived_Values] FOR UPDATE AS begin declare @BusinessUnit nvarchar(50) set @BusinessUnit = 'Updated Record -- After Update Trigger.' update [Derived_Values_Test] set --BusinessUnit = i.BusinessUnit --,Questions = i.Questions Answer = i.Answer from [Derived_Values] inner join inserted i on [Derived_Values].BusinessUnit = i.BusinessUnit and [Derived_Values].Questions = i.Questions end go CREATE TRIGGER trgAfterDelete ON [Derived_Values] FOR DELETE AS begin delete [Derived_Values_Test] from [Derived_Values_Test] inner join deleted d on [Derived_Values_Test].BusinessUnit = d.BusinessUnit and [Derived_Values_Test].Questions = d.Questions end go insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21') insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22') UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11'); UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12'); UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21'); UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22'); --delete Derived_Values;

Et alors: 

 SELECT * FROM Derived_Values; go select * from Derived_Values_Test; BUSINESSUNIT QUESTIONS ANSWER BU1 Q11 Updated Answers A11 BU1 Q12 Updated Answers A12 BU2 Q21 Updated Answers A21 BU2 Q22 Updated Answers A22 BUSINESSUNIT QUESTIONS ANSWER BU1 Q11 Updated Answers A11 BU1 Q12 Updated Answers A12 BU2 Q21 Updated Answers A21 BU2 Q22 Updated Answers A22