當欄位型態為TEXT時，建立TRIGGER的語法

CREATE TRIGGER tr住診病歷摘要記錄A ON 住診病歷摘要檔
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF (NOT EXISTS (SELECT P.counter FROM 住診病歷摘要檔 P, inserted I WHERE P.counter = I.counter))
insert into 住診病歷摘要暫存檔(工作類別,原counter,住診檔_counter,病摘內容,病摘類別代碼,經手人代號,病摘種類代碼,異動日期時間,確認否,科別代碼)
select 'A',counter,住診檔_counter,病摘內容,病摘類別代碼,經手人代號,病摘種類代碼,異動日期時間,確認否,科別代碼
from inserted
IF (NOT EXISTS (SELECT E.counter FROM 住診病歷摘要檔 E, inserted WHERE E.counter = inserted.counter))
begin
INSERT INTO 住診病歷摘要檔(住診檔_counter,病摘內容)
SELECT 住診檔_counter,病摘內容 FROM inserted
end
END


CREATE TRIGGER tr住診病歷摘要記錄U ON 住診病歷摘要檔
INSTEAD OF update
AS
BEGIN
SET NOCOUNT ON
IF ( EXISTS (SELECT P.counter FROM 住診病歷摘要檔 P, inserted I WHERE P.counter = I.counter))
insert into 住診病歷摘要暫存檔(工作類別,原counter,住診檔_counter,病摘內容,病摘類別代碼,經手人代號,病摘種類代碼,異動日期時間,確認否,科別代碼)
select 'U',counter,住診檔_counter,病摘內容,病摘類別代碼,經手人代號,病摘種類代碼,異動日期時間,確認否,科別代碼
from inserted
IF ( EXISTS (SELECT E.counter FROM 住診病歷摘要檔 E, inserted WHERE E.counter = inserted.counter))
begin
update 住診病歷摘要檔 set
住診檔_counter=I.住診檔_counter,
病摘內容=I.病摘內容
from 住診病歷摘要檔 A join inserted I
on a.counter=I.counter
end
END