Insert Tigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Tigger

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-06-29 : 10:54:21

Hi
I have table named TCV that has columns (xID, xName, xSeq)
this table has Primary Key called xID
xID is bigint and NOT Identity (there is another column named xSeq)

I want to INSERT INTO TCV from another table called MyT

I use

INSERT INTO TCV (xName)
SELECT mName FROM MyT WHERE mLevel = 3

the problem that this insert will not work coz the Key does not accept null

I tried to use Instead of Insert Trigger to set this value to (Max + 1)

This is my trigger

ALTER TRIGGER [dbo].[TCV_TgInsert]
ON [dbo].[TCV]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @MaxID bigint
SELECT @MaxID= ISNULL(MAX(xID), 0) + 1 FROM TCV

INSERT INTO TCV (xID, xName)
SELECT @MaxID, xName FROM Inserted

SET NOCOUNT ON;
END

this code will make the insert statement returns this error

Msg 2627, Level 14, State 1, Procedure TCV_TgInsert, Line 9
Violation of PRIMARY KEY constraint 'PK_TCV'. Cannot insert duplicate key in object 'dbo.TCV'.
The statement has been terminated.

how can i fix this, plz note that xID can NOT be IDENTITY

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 11:10:50

inserted may contains more than one record. And when that happened, you are inserting all these records with same @MaxID

try this


SELECT @MaxID + row_number() over(order by xName), xName 
FROM   Inserted

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 13:27:40

Suggest you read about triggers to understand the concept & how they work

http://doc.ddart.net/mssql/sql70/create_8.htm

nice123ej
Starting Member

48 Posts

Posted - 2008-06-29 : 21:16:14

khtan
thx it is working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources