April 8, 2012 at 11:59 am
Basically im not great with database design and I need a trigger which takes the updated data from one table (Training) and puts the old data into another table (trainingHist). Im trying the below code at the moment
CREATE 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 * FROM UPDATED;
END;
website below for ERD
i.imgur.com/GUDuO.jpg
April 8, 2012 at 12:28 pm
No such table as updated.
In an update trigger the new values are in inserted and the old values in deleted.
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 8, 2012 at 12:36 pm
sorry i knew that it should be from DELETED forgot to upload new version but yes still does not work
April 8, 2012 at 4:03 pm
Define 'does not work' please.
Throws an error?
Inserts wrong data?
Inserts no data?
Transforms the server into a pool of molten metal?
Something else?
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 5:43 am
I building a c# windows application and trying to use the trigger to put the old data into the traininghist table. The error im recieving from visual studio is 'Conversion failed when converting date and/or time from character string.'.
I think it is to do with the columns and column list as you see what im updating and what is being inserting into the training hist. I think it because im only inserting 5 columns when there are actually 7 in training hist. Im not sure how to edit the trigger to do this as i need the staffNo and to create a trainingHist No.
April 9, 2012 at 6:12 am
The first thing is to specify the columns in the select as well as the insert. You have this:
INSERT INTO trainingHist(training_Type, completed_Date, Expiry_Date, Qualified_Unqualified, training_No, staff_No)
Select * FROM deleted;
Rather than SELECT * , put the column names that match the columns in the insert clause.
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 7:15 am
lukebaker (4/9/2012)
I building a c# windows application and trying to use the trigger to put the old data into the traininghist table. The error im recieving from visual studio is 'Conversion failed when converting date and/or time from character string.'.I think it is to do with the columns and column list as you see what im updating and what is being inserting into the training hist. I think it because im only inserting 5 columns when there are actually 7 in training hist. Im not sure how to edit the trigger to do this as i need the staffNo and to create a trainingHist No.
As Gail said, use an explicit column list in both the insert and select statements. The error is telling you are trying to put a non-data value into a data column and it is failing. Likely because you aren't explicitly listing the columns in your select so a non-date column is being inserted into a data column
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
April 9, 2012 at 7:21 am
It could also be that the history table isn't defined EXACTLY like the base table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 9, 2012 at 7:46 am
TheSQLGuru (4/9/2012)
It could also be that the history table isn't defined EXACTLY like the base table.
The OP says there are 7 columns in the hist table and 5 in the normal table, so that's kinda a given.
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 8:21 am
Im thinking of doing something like the below
CREATE TRIGGER tg_UpdateTraining
ON Training
AFTER UPDATE
AS
BEGIN
INSERT INTO trainingHist
(
AdditionalColumn1,
AdditionalColumn2,
training_Type,
completed_Date,
EXPIRY_DATE,
Qualified_Unqualified,
training_No,
staff_No
)
SELECT
0 AS AdditionalColumn1,
1 AS AdditionalColumn2,
training_Type,
completed_Date,
EXPIRY_DATE,
Qualified_Unqualified,
training_No,
staff_No
FROM
DELETED;
END;
but the additional columns are trainingHist_No and staff_No so i need to get the staff_No from the staff table aswell?
April 9, 2012 at 8:28 am
Are the columns actually called 'AdditionalColumn1' and 'AdditionalColumn2'?
Is there a Staff_no column in the Training table? If not, you'll have to join deleted to whatever table does have the staff_no column.
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 8:33 am
No they are not called additionalcolumn, the one is the staff_No and the other is the primary key of the trainingHist table (trainingHist_No) so yes I think the join with the deleted is required.
April 9, 2012 at 8:37 am
Then those 'additionalcolumn' columns need to come out of the insert and the select clause.
The column list on the insert is the list of columns you're populating. Leave out any that you want to have the default values (like any identity columns)
The column list on the select must list the source of the data that you want to be inserted, the columns must be in the same order as in the insert.
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:35 am
Ok so it has been SOLVED. Would like to thank all the people that helped. Below is the code that worked for me.
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,
training_No,
staff.staff_No
FROM
DELETED, staff;
END;
April 9, 2012 at 10:40 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.
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,
training_No,
staff.staff_No
FROM
DELETED, staff;
END;
Are you sure? This looks like it is going to generate a cartesion product between the DELETED and staff tables.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply