December 10, 2008 at 10:06 pm
In my trigger I'm retrieving values from table named "X" based on id. After retrieving, with those values I'm updating the Table "Y" columns.After completion of update i want to delete those particular rows from my Table "X".
Thanks in advance.
December 10, 2008 at 10:10 pm
This almost sounds like homework or an interview question.
You should write some code, make an attempt, and if you don't understand what works or doesn't, we'll help. But we don't do all your work for you.
December 10, 2008 at 10:28 pm
no i'm trying this but while deleting i'm getting error.
I'll explain my actual scenario , my third party interfave inserting values to my tables "X" and "Y". I want to update some columns in table "X" with the values i'm getting in Table "Y".
For example :
"Y" Table cols are id,allergy
"X" Table cols are id,allergy1,allergy2,allergy3
I wrote the after insert trigger on "X" Table.In that trigger i'm taking the allergy values of a paticular id(may those will come 1 to 3 values mean columns).After that i'm updating those values in my "X" table allergy1,allergy2,allergy3 columns.
After updating i want to delete those columns from "Y" table.
If i don't write delete statement in the trigger it was working fine.If i write delete statement i'm getting error.both insertions are failing
December 10, 2008 at 10:34 pm
whats the error?
"Keep Trying"
December 10, 2008 at 10:41 pm
Insert into Y(id,allergy1,allergy2,allergy3) values(?,?,?)
ODBC Driver Error:
Invalid length parameter passed to the substring function.
If we remove the delete statement we are not getting any error.
December 11, 2008 at 4:05 am
You are using a substring function in your code. check the value of the length parameter being passed.
"Keep Trying"
December 11, 2008 at 6:54 am
No one can accurately answer this question without seeing the code you are using.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2008 at 7:15 am
I've two tables "Medication" and "Allergy"
Medication table has cols: MedCardID,alg1,alg2,alg3,alg4,alg5
and
Allergy table has cols : CardID,allergey
After inserting the values into allergy table and Medication table.I want to update the allergies in medication table(at the time of insertion these values are null).suppose that in medication table i've 5 rows with some cardid, and in allergy table i've 3 rows with that same card id.
Now i want to take those 3 rows from allergy table( i mean 1st row value of allergy col is alg1,2nd val of allergy col is alg2,3rd val of allergy col is alg3 ,........like that)and update all the rows(5 rows) in medication table.
But i want to delete the rows in Allergy table for that CardID after updation complete in Medication table.
Below is my trigger
ALTER TRIGGER [trgMedication]
ON [dbo].[Medication]
AFTER INSERT
AS
BEGIN
Declare @MedCardId varchar(50),
@DischargeDate varchar(10),
@GroupNo varchar(50),
@Diagnosis varchar(50),
@EffDate datetime,
@DrugStatus int,
@MedDescription varchar(255),
@Strength varchar(50),
@MedDes varchar(100),
@InsertedMedDescription varchar(255)
Select @MedCardId=MedCardId,
@DischargeDate=DischargeDate,
@EffDate=EffDate,
@MedDescription=MedDescription,
@InsertedMedDescription=MedDescription
From Inserted
-------------------------------
declare @alg1 varchar(255)
declare @alg2 varchar(255)
declare @alg3 varchar(255)
declare @alg4 varchar(255)
declare @alg5 varchar(255)
DECLARE @Allergies VARCHAR(8000)
SET @Allergies = ''
SELECT @Allergies = Allergy + '$' + @Allergies
FROM (Select distinct Allergy from Allergy where CardID=@MedCardId and Allergy is not null) A
--Print @Allergies
if @Allergies is not NULL
Begin
SET @alg1 = substring(@Allergies, 1, patindex('%$%', @Allergies)-1)
SET @Allergies = substring(@Allergies, patindex('%$%', @Allergies)+1, len(@Allergies))
--Print @alg1
if @Allergies <> ''
SET @alg2 = substring(@Allergies, 1, patindex('%$%', @Allergies)-1)
SET @Allergies = substring(@Allergies, patindex('%$%', @Allergies)+1, len(@Allergies))
--Print @alg2
if @Allergies <> ''
SET @alg3 = substring(@Allergies, 1, patindex('%$%', @Allergies)-1)
SET @Allergies = substring(@Allergies, patindex('%$%', @Allergies)+1, len(@Allergies))
--Print @alg3
if @Allergies <> ''
SET @alg4 = substring(@Allergies, 1, patindex('%$%', @Allergies)-1)
SET @Allergies = substring(@Allergies, patindex('%$%', @Allergies)+1, len(@Allergies))
--Print @alg4
if @Allergies <> ''
SET @alg5 = substring(@Allergies, 1, patindex('%$%', @Allergies)-1)
SET @Allergies = substring(@Allergies, patindex('%$%', @Allergies)+1, len(@Allergies))
--Print @alg5
End
else
Select top 1 @GroupNo=PlanId,@Diagnosis=Diagnosis, @DrugStatus = DrugStatus from PatientLog
where
CardId=@MedCardId
order by ModifiedDate desc
Update Medication set
GroupNo=@GroupNo,
Diagnosis=@Diagnosis,
DischargeDate=@DischargeDate,
MedDescription=@MedDes,
Strength=@Strength,
MedRef=@MedDescription,
Alg1=@alg1,
Alg2=@alg2,
Alg3=@alg3,
Alg4=@alg4,
Alg5=@alg5
where (MedCardId=@MedCardId and MedDescription=@InsertedMedDescription) OR (MedCardId=@MedCardId and MedDescription is NULL)
delete from allergy where CardID=@MedCardId
END
December 11, 2008 at 7:42 am
I'm not going to address all th code in this post, but will quickly say that you should rethink the trigger because it will only work for a single row insert. If a set of data is inserted it will only transfer the data for one run, and you are not guaranteed which row that will be. Check out this article[/url] for some tips.
I will take some time to more closely examine the code as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply