Solved

Simple TRIGGER HELP NEEDED

Posted on 2011-09-17
10
272 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:SQLwatcher
10 Comments
 

Author Comment

by:SQLwatcher
Comment Utility
This is the error:  Conversion failed when converting date and/or time from character string.
My Datefields are DateTime defined.  Thank you
0
 

Expert Comment

by:mk_ananth
Comment Utility
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
 

Author Comment

by:SQLwatcher
Comment Utility
My fields are date time:  '2001-03-17 00:0000.000' format.  I have tried CONVERT without luck as well.  
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:SQLwatcher
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
Comment Utility
I am afraid I have no idea, that error message makes no sense whatsoever.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
 

Author Comment

by:SQLwatcher
Comment Utility
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
 

Author Closing Comment

by:SQLwatcher
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now