SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE  SERVICE(
CODE_SERVICE  varchar(50)primary key (CODE_SERVICE),
NOM_SERVICE varchar(50),
BATIMENT int, 
DIRECTEUR int, 
);


CREATE TABLE INFIRMIER
(
NUM_INF int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
CODE_SERVICE varchar(50),
ROTATION varchar(50), 
SALAIRE money,
CONSTRAINT FK_INF_CODE_SERVICE FOREIGN KEY (CODE_SERVICE) REFERENCES SERVICE(CODE_SERVICE)
);
CREATE TRIGGER TRIG1
ON INFIRMIER
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  -- Change it to an INSERT 
  SELECT * 
  FROM INSERTED I INNER JOIN Service S 
  ON I.CODE_SERVICE = S.CODE_SERVICE;
END
INSERT INTO Service VALUES
('Service1', 'ServiceName1', 1, 1);

INSERT INTO Infirmier(CODE_SERVICE, ROTATION, SALAIRE) VALUES
('Service1', 'Rotation', 100.00);


NUM_INF
CODE_SERVICE
ROTATION
SALAIRE
CODE_SERVICE
NOM_SERVICE
BATIMENT
DIRECTEUR


1
Service1
Rotation
100.0000
Service1
ServiceName1
1
1