如何编写更新触发器查询 [英] How to write Update Trigger Query

问题描述

这里我为我的表写了一个插入查询，我想写下面编码的更新查询，你可以请任何一个帮助吗？

Hi,

Here I have wrote an insert query for my table, I want write update query for below coding can you please any one help?

alter trigger trigPatientinformationinsert
on PatientsInformations
for insert,update
as
declare @PatientOutid uniqueidentifier,@Personid uniqueidentifier,@PatientChartNumber nvarchar(250),@PatientID2 nvarchar(250),@LastName nvarchar(250),
@MiddleName nvarchar(250),@FirstName nvarchar(250),@BirthDate datetime,@Sex nvarchar(250),@Patientflag bit,@SignatureOnFile bit,@Street1 nvarchar(250),
@Street2 nvarchar(250),@City nvarchar(250),@State nvarchar(250),@ZipCode nvarchar(250),@Home nvarchar(250),@HomeAddressid nvarchar(250),@stateid nvarchar(250),@Addressid nvarchar(250),@GenderId nvarchar(250),
@Cell nvarchar(250),@CreatedBy uniqueidentifier,@CreatedOn datetime,@ModifiedOn datetime,@ModifiedBy uniqueidentifier
/* Set New id Created by using newid() */
set @PatientOutid=NEWID()
/* fetching personid from patinetinformation table */
set @Personid=(select Personid from inserted)
/* get account number from patientinformation by using person id to get perticular data */
set @PatientChartNumber=(select AccountNumber from PatientsInformations where PersonID=@Personid)
set @PatientID2=(select SSN from PersonsInformations where PersonID=@Personid)
set @LastName=(select lastname from PersonsInformations where PersonID=@Personid)
set @MiddleName=(select MiddleName from PersonsInformations where PersonID=@Personid)
set @FirstName=(select FirstName from PersonsInformations where PersonID=@Personid)
set @BirthDate=(select DateOfBirth from PersonsInformations where PersonID=@Personid)
/* Getting relationship data from one table to another, so we declare fetching record id to get exact values from another 
table */
set @GenderId=(select GenderId from PersonsInformations where PersonID=@Personid)
set @sex=(select GenderName from Genders where  GenderId=@GenderId)
/* Getting relationship data from one table to another, so we declare fetching record id to get exact values from another 
table */
set @HomeAddressid=(select HomeAddressId from PersonsInformations where PersonID=@Personid)
/* get data from one table relationship to second table */
set @Street1=(select Address1 from Addresses where AddressId=@HomeAddressid)
set @Street2=(select Address2 from Addresses where AddressId=@HomeAddressid)
set @City=(select City from Addresses where AddressId=@HomeAddressid)
/* Getting relationship data from one table to another, so we declare fetching record id to get exact values from another 
table */
set @stateid=(select StateId from Addresses where AddressId=@HomeAddressid)
set @State=(select Statename from States where StateId=@stateid)
set @ZipCode=(select ZipCode from Addresses where AddressId=@HomeAddressid)
set @Home=(select PhoneNo from Addresses where AddressId=@HomeAddressid)
set @Cell=(select CellPhoneNo from PersonsInformations where PersonID=@Personid)
set @CreatedBy=(select CreatedBy from inserted)
set @CreatedOn=(select CreatedOn from inserted)
set @ModifiedOn=(select ModifiedOn from inserted)
set @ModifiedBy=(select ModifiedBy from inserted)
if exists (select Lastname,FirstName,MiddleName,PatinetID2,BirthDate from PatientOutbound) 
begin
update PatientOutbound set LastName=@LastName,FirstName=@FirstName,@BirthDate=@BirthDate,Sex=@sex,Street1=@Street1,Street2=@Street2 where personid=@Personid
end
else
insert into PatientOutbound(PatientOutid,Personid,PatientChartNumber,PatientID2,LastName,MiddleName,FirstName,BirthDate,Sex,
Street1,Street2,City,[State],ZipCode,Home,Cell,CreatedBy,CreatedOn,ModifiedOn,ModifiedBy) 
values(@PatientOutid,@Personid,@PatientChartNumber,@PatientID2,@LastName,@MiddleName,@FirstName,@BirthDate,
@Sex,@Street1,@Street2,@City,@State,@ZipCode,@Home,@Cell,@CreatedBy,@CreatedOn,@ModifiedOn,@ModifiedBy)

谢谢，

Thanks,

推荐答案

仔细查看触发器，我建议你完全重新设计它。



看起来你正试图在另一个中添加或修改行表基于此表中的数据。第一个问题是，为什么要复制数据？你从手边的行收集信息并将其复制到其他地方......



但也许你有理由要复制，所以接下来就是触发器处理只有一排。如果同时更新或插入两行或更多行，则会失败，因为在这种情况下插入的表将有多行。所以你需要能够处理多行。



第三件事是你为什么要逐一收集价值，为什么不同时取出它们。



最后但并非最不重要的，为什么你实际上将值集合到变量中？最有效的方法是使用单个SQL语句来完成这项工作。



我希望触发器看起来像这个伪代码

Having a closer look on the trigger, I would advise you to redesign it completely.

It looks like you're trying to add or modify rows in another table based on the data on this table. The first question is, why do you duplicate the data? You gather the information from the row at hand and copy it elsewhere...

But perhaps you have a reason for the copy so the next thing is that the trigger handles only one row. It will fail if two or more rows are updated or inserted at the same time because the inserted table will have several rows in such case. So you need to be able to handle multiple rows.

Third thing is that why do you gather the values one by one, why not fetch them all at the same time.

Last but not least, why do you actually gather the values into variables at all? The most efficient way would be to use a single SQL statement to do the job.

I would expect that the trigger should look something like this pseudo code

...
-- insert all non-existent rows
INSERT INTO PatientOutBound (columnlist)
SELECT columnlist
FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM PatientOutBound pob WHERE joining_condition)

-- update all existing rows
UPDATE pob
SET (columnlist e.g. pob.CellPhoneNo = i.CellPhoneNo)
FROM PatientOutBound pob 
INNER JOIN inserted i ON joining_condition
...

这篇关于如何编写更新触发器查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！