Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2005 insert trigger

Posted on 2010-11-29
8
Medium Priority
?
251 Views
Last Modified: 2012-06-21
Hi All,

I have a 2 simple tables
CREATE TABLE [dbo].[OCRRawData] (
      [OCRRawDataID] [int] IDENTITY (1, 1) NOT NULL ,
      [WaferNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OCRDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Wafer] (
      [OCRRawDataID] [int] IDENTITY (1, 1) NOT NULL ,
      [WaferNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OCRDate] [datetime] NULL
) ON [PRIMARY]
GO

I looking to create an insert trigger on OCRRawData table. When a record is inserted I want to update the Wafer table with the values from OCRRawData. The records will already exist in the Wafer table so it must be an update and several records can be inserted in OCRRawData at once.
the Wafer table will have other fields in it but I did not include them here because they are not needed now.
0
Comment
Question by:iftech
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:joevi
ID: 34234565
A few questions on your design before moving on to a trigger:
1) What are the functions of the two tables? Is OCRRawData on the Many side of the relationship?
2) Is the PK/FK relationship between them the 4 columns shown?
3) If there is a relationship between them why is OCRRawDataID an identity column in both?
4) How are you handling referential integrity?
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 34236397
Create trigger tr_update on OCRRawData after insert as update Wafer set wafernumber = inserted.wafernumber from wafer  inner join inserted  on wafer.ocrrawdataid= inserted.ocrrawdataid
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34238738
Hi,

Wanna insert data of one table to another using TRigger then you can get the inserted row in "Inserted" Table and insert requiredcolumns to another table.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 9

Expert Comment

by:sarabhai
ID: 34238900
which values u want to update of 'wafer' table.
 
0
 

Author Comment

by:iftech
ID: 34242474
Thanks for the response,

1) The WaferNumber in the Waffer table will be unique. The WaferNumber in the OCRRawData may be duplicated but I can delete inserted records after the trigger runs to ensure no duplicates in the table if needed.
2)No, there is  no relationship with PK/FK between the tables they will join on the WaferNumber
3)Wafer table is a sample table the actual identity of table will be different

I want to insert the whole row only if the WafferNumber in OCRRawData does not exist in the Wafer table.  if it is there I want to update the fields in the Wafer table from the fields in the OCRRawData table.    
0
 
LVL 4

Accepted Solution

by:
joevi earned 2000 total points
ID: 34242852
I left Inserts/Updates for OCRRawDataID since it's an identity column in both tables but the trigger below can be modified to accommodate any changes.

Create TRIGGER trOCRRawData
   ON OCRRawData
   AFTER INSERT
AS
BEGIN
      SET NOCOUNT ON;
      --Insert New Values
      INSERT INTO Wafer (WaferNumber, FrontSideLasermark, OCRDate)
      SELECT     Inserted.WaferNumber, Inserted.FrontSideLasermark, Inserted.OCRDate
      FROM         Inserted LEFT OUTER JOIN
                      Wafer ON Inserted.WaferNumber = Wafer.WaferNumber
      WHERE     (Wafer.WaferNumber IS NULL)
      --Update Existing Values
      UPDATE    Wafer
      SET  FrontSideLasermark = Inserted.FrontSideLaserMark, OCRDate = Inserted.OCRDate
      FROM         Inserted INNER JOIN
    Wafer ON Inserted.WaferNumber = Wafer.WaferNumber
END
0
 
LVL 4

Expert Comment

by:joevi
ID: 34243027
Correction:
I left OUT Inserts/Updates for OCRRawDataID since it's an identity column in both tables ....
and
you'll obviously want to add error trapping to suite your needs.

0
 

Author Closing Comment

by:iftech
ID: 34249188
very nice thank you
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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