October 19, 2004 at 10:28 am
I have a select statement that should generate a unique field valued based upon officecostctr, rcptdate and receiptid. The officecostctr is being pulled from a table called "Offices". The statement is written as follow:
(SELECT
officecostctr + cast(datepart(yy,rcptdate) as varchar) + replicate('0',5-len(receiptid)) + cast(receiptid as varchar) AS ReceiptID
FROM receipts inner join offices on Receipts.officeid = Offices.officeid)
1) How can I use this same format as the unique identifier in place of an autonumber or integer that is generated when a new record is inserted into a table. And 2), is this done by using a TRIGGER, and if so, how? The table has the following fields:
CREATE TABLE [dbo].[Receipts] (
[ReceiptID] [char] (12) AS NULL ,
[CityID] [int] NULL ,
[RcptDate] [datetime] NOT NULL ,
[RcptAmt] [numeric](18, 0) NOT NULL ,
[OfficeID] [int] NOT NULL ,
[PymtTypeID] [int] NOT NULL ,
[CheckNum] [char] (10) AS NULL ,
[RecdFrom] [char] (10) AS NOT NULL ,
[CaseNumber] [varchar] (15) AS NULL ,
[CasePlaintiff] [varchar] (30) AS NULL ,
[CaseDefendant] [varchar] (30) AS NULL ,
[CourtName] [varchar] (30) AS NULL ,
[SubpeonaDate] [datetime] NULL ,
[EmplID] [char] (5) AS NOT NULL ,
[Waived] [bit] NULL ,
[WaivedReason] [varchar] (512) AS NULL ,
[Voided] [bit] NULL ,
[VoidDate] [datetime] NULL ,
[VoidReason] [varchar] (512) AS NULL ,
[Timestamp] [datetime] NULL
) ON [PRIMARY]
GO
I also have another table called ReceiptDetails that is linked by the ReceiptID.
CREATE TABLE [dbo].[ReceiptDetails] (
[ReceiptID] [char] (12) AS NOT NULL ,
[FeeTypeID] [int] NOT NULL ,
[FeeCost] [money] NOT NULL ,
[FeeQuantity] [numeric](18, 0) NOT NULL ,
[FeeTotal] [money] NOT NULL ,
[CheckNum] [char] (10) AS NULL ,
[Timestamp] [datetime] NULL
) ON [PRIMARY]
GO
3) With the unique key in the Receipt table, how does this same number get populated into the ReceiptDetails?
Data Example:
ReceiptID:
CityID: (1) for San Diego
RcptDate: (Autogenerated using getDate())
OfficeID: Escrow, Inc.
PymtTypeID: (2) for Check
CheckNum: 1010
RecdFrom: S1255 (employee ID number)
CaseNumber: 12345678
CasePlaintiff: Cottontail
CaseDefendant: Timberlake
CourtName: San Diego Municipal
SubpeonaDate: 10/10/2004
EmpLID: S1255
Waived: Yes or No field
WaivedReason: Unable to pay court fee
VoidDate: (Autogenerated using getDate())
VoidReason: Plaintiff plead out
October 19, 2004 at 12:09 pm
Are you bulk inserting? If not, you can get teh value first and then include it in the insert statement.
October 19, 2004 at 2:28 pm
I'm not doing a bulk insert. It's one record at a time, so how can I get the value first and then include it in my insert statement.
October 20, 2004 at 12:50 pm
You're using receiptid to generate receiptid. Looks fishy.
A trigger on insert is a way to generate the receiptid. However, the only true way to get the trigger-generated receiptid for the second insert, is to select it afterward using some other unique identifier. Does the table have another unique id?
Best way to handle this situation is to use a stored procedure that does the initial insert and returns the generated receiptid in an output parameter.
Another alternative is to generate the receiptid in whatever program that's executing the inserts.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply