Click here to Skip to main content
16,001,543 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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?

SQL
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,
Posted
Updated 19-Jul-15 20:48pm
v2
Comments
Thanks7872 20-Jul-15 2:27am    
Dumping just a huge code block won't make any sense.
Wendelius 20-Jul-15 2:51am    
As far as I can see you already have an update statement. What is the problem with that?
kalaiselvan Indirajith 20-Jul-15 3:27am    
Hi Mika,

Thanks for your suggestions, I am new from trigger now I am learning how to create and update like this... so that I have ask question. Thanks for your explain and given suggestions.
Wendelius 20-Jul-15 3:41am    
Glad it helped :)

1 solution

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
SQL
...
-- 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
...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900