April 9, 2012 at 10:42 am
lukebaker (4/9/2012)
Ok so it has been SOLVED. Would like to thank all the people that helped. Below is the code that worked for me.
That is definitely not going to work....
If there are 2 rows in deleted and 10 rows in staff, that will insert 20 rows into the history table, 2 rows for each staff row...
Somehow I don't think that's what you want.
Read up on JOINs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2012 at 10:45 am
yer it is inserting more records than i thought actually i just thought that was down to my database structure. Any idea how i could solve this??
April 9, 2012 at 10:49 am
GilaMonster (4/9/2012)
Read up on JOINs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2012 at 11:06 am
Right this time i think i have solved it.
ALTER TRIGGER tg_UpdateTraining
ON Training
AFTER UPDATE
AS
BEGIN
INSERT INTO trainingHist
(
training_Type,
completed_Date,
expiry_Date,
qualified_Unqualified,
training_No,
staff_No
)
SELECT
training_Type,
completed_Date,
expiry_Date,
DELETED.qualified_Unqualified,
DELETED.training_No,
staff.staff_No
FROM
DELETED, staff,staffTraining
WHERE
DELETED.training_No = staffTraining.training_No
AND
staff.staff_No = staffTraining.staff_No;
END;
April 9, 2012 at 11:08 am
It'll work, but that's a bad way to do it.
Read up on joins (as in the SQL keyword JOIN)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply