Autonumber Values and Triggers
-
I am hoping to use a Trigger to create some billing transactions whenever records are added to a table. When these records are added a sequential tracking number (autonumber) is assigned. My question is this:
On an Insert Trigger, is the autonumber value available to me from the Inserted table? I need to store this tracking number with the billing record.
Thanks,
Bill
-
Yes it is.
-
Hey !
Currently, i have this weird Error message :-
" Cannot insert explicit value for Identity column in #Test______________00010000017 When Identity_Insert is set to OFF "
Can any1 here Guide me - Its Urgent Plz ...........
Please Try to Simulate a Temporary Table with :- statement & tell me ur Solution :-
CREATE TABLE #Test (
Counter int IDENTITY (1,1) NOT NULL,
)
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services -
Sounds like your trigger is attempting to insert the inserted table's identity value into another table's identity column, hence the "Identity_Insert is set to OFF" error.
CREATE TABLE #Test (Counter int IDENTITY (1,1) NOT NULL)
SET IDENTITY_INSERT #Test ON
INSERT INTO #Test
SELECT id -- identity column in trigger's inserted table
FROM inserted
SET IDENTITY_INSERT #Test OFF
Instead:
CREATE TABLE #Test (Counter int NOT NULL)
INSERT #Test
SELECT id -- identity column in trigger's inserted table
FROM inserted
Andy
-
hey Andy/David
thanx a lot for ur Post ---
BUT, theres another Problem ?
After i put
SET Identity_Insert ON
& SET Identity_Insert OFF
--- IT SAYS
"
String or Binary data would be Truncated.
The statement has been terminated.
"
heres my Full StoredProc :--- sp_dsp_Visualiser
ALTER PROCEDURE dbo.sp_dsp_Visualiser
AS
CREATE TABLE [#Test] (
[Counter] [int] IDENTITY (1, 1) NOT NULL ,
[AYear] [int], --NULL ,
[SequentialYear] [int], --NULL ,
[StudNo] [int], --NULL ,
[StudName] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StudRollNo] [int], --NULL ,
[StudGRNo] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Std] [int], --NULL ,
[Div] [varchar] (1), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCode] [int], --NULL ,
[SubjectName] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MTMarks] [int], --NULL ,
[MTGrades] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MTSubjectRank] [int], --NULL ,
[MTTotalMarks] [int], --NULL ,
[MTTotalMarksOutOf] [int], --NULL ,
[MTTermRank] [int], --NULL ,
[MTTermDivision] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TTMarks] [int], --NULL ,
[TTGrades] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TTSubjectRank] [int], --NULL ,
[TTTotalMarks] [int], --NULL ,
[TTTotalMarksOutOf] [int], --NULL ,
[TTTermRank] [int], --NULL ,
[TTTermDivision] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTMarks] [int], --NULL ,
[FTGrades] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTSubjectRank] [int], --NULL ,
[FTTotalMarks] [int], --NULL ,
[FTTotalMarksOutOf] [int], --NULL ,
[FTTermRank] [int], --NULL ,
[FTTermDivision] [varchar] (50), --COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StudentAttitude] [varchar] (100) --COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
SET IDENTITY_INSERT #Test ON
INSERT INTO #Test(Counter,AYear,SequentialYear,StudNo,StudRollNo,StudName,StudGRNo,Std,Div,SubjectCode,SubjectName,MTMarks,MTGrades,MTSubjectRank,MTTotalMarks,MTTotalMarksOutOf,
MTTermRank,MTTermDivision,TTMarks,TTGrades,TTSubjectRank,TTTotalMarks,TTTotalMarksOutOf,TTTermRank,TTTermDivision,FTMarks,FTGrades,FTSubjectRank,FTTotalMarks,FTTotalMarksOutOf,
FTTermRank,FTTermDivision,StudentAttitude)
SELECT A.Ctr,A.AcademicYear_Code,A.AcademicYear_Code-1,A.Student_No,A.Student_RollNo,A.Student_Name,B.Student_GrNo,A.Student_Std,A.Student_Division,A.Subject_Code,A.Subject_Name,
A.MTMarksObtained,A.MTGradeObtained,A.MTSubjectRank,A.MTTotalMarksObtained,A.MTTotalMarksOutOf,A.MTRank,A.MTDivision,
A.TTMarksObtained,A.TTGradeObtained,A.TTSubjectRank,A.TTTotalMarksObtained,A.TTTotalMarksOutOf,A.TTRank,A.TTDivision,
A.FTMarksObtained,A.FTGradeObtained,A.FTSubjectRank,A.FTTotalMarksObtained,A.FTTotalMarksOutOf,A.FTRank,A.FTDivision,
A.Attitud_Student FROM trn_Progress_Card_Data A
LEFT OUTER JOIN Mst_Student B
ON A.Student_No=B.Student_No
SET IDENTITY_INSERT #Test OFF
--Where
--A.School_Code=@iSchoolCode
--And A.AcademicYear_Code=@iYear
--And A.Student_Class =@iclass
--And A.Student_Division =@strDivision
SELECT StudNo,StudName,* FROM #Test
DROP TABLE #Test
GO
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services -
String or Binary data would be Truncated.
The statement has been terminated.
Have a look at CAST and CONVERT in BOL. This error typically arises when you try, for example, to push a VARCHAR(100) into a VARCHAR(50).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Also why do you want to use set identity insert on?
Are you logging something into another table?
Are you inserting child records?
-
I agree, it's odd that you would want to insert into another table's identity column...
If you were doing something with (for example) orders and order details, linking them on
orders.orderID int identity
orderDetails.orderID int identity
then that's wrong - you should not have the identity attribute on the orderDetails.orderID column.
But I'm just assuming what you are doing
-
I believe structure of the CREATE TABLE #Temp does not match the query, why not:
IF (SELECT OBJECT_ID('tempdb..#Test')) > 0
EXEC ('Drop TABLE tempdb..#Test')
SELECT A.Ctr,A.AcademicYear_Code,A.AcademicYear_Code-1,A.Student_No
,A.Student_RollNo,A.Student_Name,B.Student_GrNo,A.Student_Std
,A.Student_Division,A.Subject_Code,A.Subject_Name
,A.MTMarksObtained,A.MTGradeObtained,A.MTSubjectRank
,A.MTTotalMarksObtained,A.MTTotalMarksOutOf,A.MTRank,A.MTDivision
,A.TTMarksObtained,A.TTGradeObtained,A.TTSubjectRank
,A.TTTotalMarksObtained,A.TTTotalMarksOutOf,A.TTRank,A.TTDivision
,A.FTMarksObtained,A.FTGradeObtained,A.FTSubjectRank
,A.FTTotalMarksObtained,A.FTTotalMarksOutOf,A.FTRank,A.FTDivision
,A.Attitud_Student
INTO #Test
FROM trn_Progress_Card_Data A
LEFT OUTER JOIN Mst_Student B ON A.Student_No=B.Student_No
SELECT * FROM #Test
Andy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply