Solved

Simple TRIGGER HELP NEEDED

Posted on 2011-09-17
10
280 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

756 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