/* Création des tables */
CREATE TABLE Employe (
	Id_Emp int NOT NULL,
	Nom_Emp varchar(20),
	Prenom_Emp varchar(20),
	PRIMARY KEY (Id_Emp)
);

ALTER TABLE Employe
ADD UNIQUE (Id_Emp);

CREATE TABLE Secretaire (
	Id_Emp int NOT NULL,
	e_mail varchar(100),
	telephone varchar(10),
	PRIMARY KEY (Id_Emp),
	FOREIGN KEY (Id_Emp) REFERENCES Employe(Id_Emp)
	);


/* Insertion des données */

INSERT INTO Employe VALUES (1,'Chirac','Bernadette');
INSERT INTO Employe VALUES (2,'Veil','Simone');
INSERT INTO Employe VALUES (3,'Kardashian','Kim');
INSERT INTO Employe VALUES (4,'Granger','Hermione');
INSERT INTO Employe VALUES (5,'Merkel','Angela');

INSERT INTO Secretaire VALUES (1,'Bernadettechirac@hotmail.fr','0123456789');
INSERT INTO Secretaire VALUES (2,'Simoneveil@hotmail.fr','0223456788');
INSERT INTO Secretaire VALUES (3,'Kimkardashian@hotmail.fr','0323456787');
INSERT INTO Secretaire VALUES (4,'Hermionegranger@hotmail.fr','0423456786');
INSERT INTO Secretaire VALUES (5,'Angelamerkel@hotmail.fr','0523456785');
SELECT * From Employe
SELECT * from Secretaire
/* Création de la vue */

CREATE VIEW vue_secretaire
AS
SELECT e.Id_Emp, e.Nom_Emp, e.Prenom_Emp, s.e_mail, s.telephone
FROM Employe e, Secretaire s
WHERE e.Id_Emp = s.Id_Emp

select * FROM vue_secretaire

/* Create Tr_Stop_Insert*/

CREATE TRIGGER Tr_stop_insert
ON Secretaire
Instead of Insert
AS
	BEGIN
	RAISERROR('Insertion impossible',10,1)
	ROLLBACK TRANSACTION
	END

/* Test */
Insert Into Secretaire Values(8,'Medecis','Catherine');
/* Drop du trigger Tr_stop_insert si nécessaire */
DROP Trigger Tr_stop_insert

CREATE Trigger Tr_inserevue
on vue_secretaire
instead of insert
as
BEGIN
	INSERT INTO Employe
	SELECT i.Id_Emp,i.Nom_Emp, i.Prenom_Emp
	FROM inserted i

	Insert into Secretaire
	Select i.Id_Emp,i.e_mail, i.telephone
	FROM inserted i
END

SELECT * from vue_secretaire

DISABLE TRIGGER Tr_stop_insert on Secretaire
ENABLE TRIGGER Tr_stop_insert on Secretaire
INSERT INTO vue_secretaire VALUES (9,'Bernard','Laurence','Bernardlaurence@hotmail.fr','0601020304')

Select * from Secretaire