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 |