iftech
asked on
SQL 2005 insert trigger
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_
[FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[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_
[FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[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.
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_
[FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[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_
[FrontSideLasermark] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[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.
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
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.
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.
which values u want to update of 'wafer' table.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
very nice thank you
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?