Solved

Simple TRIGGER HELP NEEDED

Posted on 2011-09-17
10
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Author Comment

by:SQLwatcher
ID: 36554952
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
ID: 36555094
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
ID: 36555253
My fields are date time:  '2001-03-17 00:0000.000' format.  I have tried CONVERT without luck as well.  
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36556760
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
ID: 36556765
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
 

Author Comment

by:SQLwatcher
ID: 36561899
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
ID: 36564057
I am afraid I have no idea, that error message makes no sense whatsoever.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36564541
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
ID: 36564630
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
ID: 36570621
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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