1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| CREATE TRIGGER E_IU_EMP
ON V_PRS_EMP
INSTEAD OF INSERT
AS
DECLARE @INSERT TABLE (PRS_ID INT,
PRS_NOM CHAR(32),
PRS_PRENOM VARCHAR(25),
EMP_MATRICULE CHAR(8));
DECLARE @ID INT;
SELECT @ID = IDENT_CURRENT('T_PERSONNE_PRS') + COUNT(*)
FROM inserted;
DBCC CHECKIDENT ('T_PERSONNE_PRS', RESEED , @ID);
WITH T AS
(SELECT @ID AS NID, PRS_NOM, PRS_PRENOM, EMP_MATRICULE,
ROW_NUMBER() OVER (ORDER BY PRS_NOM, PRS_PRENOM) AS N
FROM inserted)
INSERT INTO @INSERT
SELECT @ID - N, PRS_NOM, PRS_PRENOM, EMP_MATRICULE
FROM T;
SET IDENTITY_INSERT T_PERSONNE_PRS ON;
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_PRENOM)
SELECT PRS_ID, PRS_NOM, PRS_PRENOM
FROM @INSERT;
SET IDENTITY_INSERT T_PERSONNE_PRS OFF;
INSERT INTO T_EMPLOYE_EMP
SELECT PRS_ID, EMP_MATRICULE
FROM @INSERT;
GO |
Partager