Simple TRIGGER HELP NEEDED

Hi - I need some help with understanding the syntax of a simple Update, Insert Trigger.  I have been reading all week and just as soon as I think I have it, the insertion of data fails.  I am a student.(not asking you to do my homework) I just need help with a push in the right direction.  I will post my code.  If you could show me the errors of my way...I would be greatful!

The problem  Create a trigger that will update an audit table with information regarding when a product shipped.  I can't believe I am stuck on this simple task.  

I am using Microsoft SQL 2008 R2 for my query.  
I keep getting errors when attempting to insert the date into the product table to test the trigger.  Can you see what I have done wrong?  

I hate to ask but need to have someone show me so I can learn this!  Oh, I have an instructor..Online course.  Would like to figure this out on my own therefore I am asking here.  Thanks
CREATE TRIGGER Shipping_Audit_Trg ON Products ---Product table is primary table
FOR UPDATE, INSERT
AS
UPDATE ProductAudit ---- ProductAudit table is to contain information related to shipment, returns. itemid, memberid, productid  
SET ShippedDate = GETDATE() ---- I want to pull the date that is inserted into the Product table 
---SET returneDate = GETDATE() --- I also want to pull the date for any returns if this field is updated
FROM inserted as i
INNER JOIN ProductAudit as pa
on pa.orderID = i.orderID

Open in new window

SQLwatcherAsked:
Who is Participating?
 
Anthony PerkinsCommented:
What is happening is that the value you are using to UPDATE is not a datetime data type.  What is the name and data type of the column in the Products table you want to use to UPDATE the ProductAudit table?
0
 
SQLwatcherAuthor Commented:
This is the error:  Conversion failed when converting date and/or time from character string.
My Datefields are DateTime defined.  Thank you
0
 
mk_ananthCommented:
It is probably the Date Format issue, when converting "Saturday" to "YYYY"( Example). Check the default Date format in sql server.
 For more information http://msdn.microsoft.com/en-us/library/ms189491.aspx
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
SQLwatcherAuthor Commented:
My fields are date time:  '2001-03-17 00:0000.000' format.  I have tried CONVERT without luck as well.  
0
 
Anthony PerkinsCommented:
Incidentally I am assuming that you are not trying to UPDATE your ShippedDate with GETDATE(), but rather that is there to demostrate that the code for the TRIGGER is correct.
0
 
SQLwatcherAuthor Commented:
Hello -

The value from the product table as "shippedDate" is defined as a DateTime field.  

The field containing the ShippedDate displays as:  '2001-03-17 00:0000.000'    

Correct, I want to update the Audit table with whatever value is entered into the Products.ShippedDate field.

I attempted to simply say:

UPDATE ProductAudit
SET pa.ShippedDate = i.ShippedDate
FROM inserted as i
INNER JOIN ProductAudit as pa
on pa.orderID = i.orderID

But this does not work either.  <pout>

0
 
Anthony PerkinsCommented:
I am afraid I have no idea, that error message makes no sense whatsoever.
0
 
LowfatspreadCommented:
The field containing the ShippedDate displays as:  '2001-03-17 00:0000.000'

do you not mean  displays as:  '2001-03-17 00:00:00.000'  ? i.e. with two colons

otherwise its not a valid datetime
0
 
SQLwatcherAuthor Commented:
Yes, the date has the correct formatting.  My example was typed incorrectly.  Well then aside from the trigger not able to be tested, is the resto of it correct?  :o)

Thanks
0
 
SQLwatcherAuthor Commented:
Neither resolved my problem but I appreciate the input.  Thanks for the assistance.  Your answers did provide information regarding other elements, so that was good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.