Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Trigger

Author

Topic

winnitbaker
Starting Member

7 Posts

Posted - 2012-04-08 : 05:27:10

[img]
C:\Users\Winnie\Desktop\Database Diagram.jpg
[/img]

winnitbaker
Starting Member

7 Posts

Posted - 2012-04-08 : 05:31:15

Sorry about the image upload i could not upload my ERD with the image tags any instruction how to do this. 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 DELETED;
END;

website below for ERD
i.imgur.com/GUDuO.jpg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:06:51

the trigger code looks fine. Are you facing any issue with this?

I hope columns in columnlist are exactly same as that in Training table in the same order

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

winnitbaker
Starting Member

7 Posts

Posted - 2012-04-09 : 07:43:37

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-09 : 07:55:49

You will have to supply values for the two additional columns in the training history (or the table should be set up such that there are default values for those columns). So, for example, you could do this:

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;

Instead of using select *, explicitly specify the column names in the insert list and the select list in the correct order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:01:07

what are datatypes of completed_Date, Expiry_Date? i hope they're datetime or date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

winnitbaker
Starting Member

7 Posts

Posted - 2012-04-09 : 12:36:58

Sunita your were correct i managed to solve my issue thanks very much for all your help. Below is the code i ended up with

[CODE]
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;
[/CODE]

winnitbaker
Starting Member

7 Posts

Posted - 2012-04-09 : 12:50:44

Dam no it hasnt just found out it has caused a cartesian product any ideas how to solve this??

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-09 : 13:05:50

get rid of the

, staff

after your FROM DELETED

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 13:30:57

quote:
Originally posted by winnitbaker

Dam no it hasnt just found out it has caused a cartesian product any ideas how to solve this??

why do you need Staff table here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources