Solved

Simple TRIGGER HELP NEEDED

Posted on 2011-09-17
10
278 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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