postgreSQL 在表 1 上发生新插入时在表 2 中创建行

Question

我有一个表格appointments(id,time,patientID,doctorID,daignosis)和 PostgreSQL 中的表medical_folder(id,patientID,cure,drug_id)

我想创建一个触发器和一个函数，当有新的诊断预约时，它会在medical_folder中插入一个新行，带有新 ID，患者 ID(来自约会(，治愈 = XXX 和 drug_id=YYY

Answer 1

从触发器开始的示例 (MSSQL(

CREATE TABLE appointments 
(id int, 
patientId int, 
doctorId int, 
diagnostics varchar(200))
GO
CREATE TABLE medical_folder
(id int, 
patientId int, 
cure int, 
drug_id int)
GO
CREATE TRIGGER InsteadTrigger on appointments
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO medical_folder
       SELECT ISNULL((SELECT MAX(id) from medical_folder),0)+1, patientId, 0, 0
       FROM inserted
END;
GO
INSERT INTO appointments
VALUES 
(1,1,1,'YES')
GO 
SELECT * FROM medical_folder