Need a Solution

Hi,

How can i define in SqlServer an way to make my identity's columns folow somekind of rule.

I need to define it in sqlserver not in my application code.
So basically what i need is to maintain the inserts i have , assuming that ids are automatic, but in the database being able to modyfing them.
Using trigers or other way, please help me... :)

thanks-.-I assume you don't want to use Sql Server's Identity="Yes" for an int field.

The alternative would be to use a Trigger that fires on Insert that will populate your identity column. The code inside the trigger can follow whatever rule you can code in Transact-Sql.

At least the last time I used Oracle you had to do it that way anyway.

CREATE TRIGGER [MyIdentifyTrigger] ON dbo.YourTable
FOR INSERT
AS
-- Figure out identify value and set column in "inserted" record to that value.

|||Hi,

No, my problem is that i have the Identity set to "Yes" in the fields. I've already seen that with SET IDENTITY_INSERT to ON i can explicit the value to the field with identity.

No, i just need to know the syntax of the trigger to do all of this, when i insert.

I need this because all my inserts in the application assumes auto ids, and now i need to control the value inserted in those field, but i cant go to the code, so must do this in database level.

thanks|||You should be able to accomplish this with an INSTEAD OF trigger. Something like this:

CREATE TRIGGER Insert_Data ON Test
INSTEAD OF INSERT
AS
DECLARE @.ID int, @.Col_SomeText varchar(50)
SELECT @.ID=ID, @.Col_SomeText = SomeText FROM inserted
IF @.ID IS NULL
 BEGIN
 BEGIN TRANSACTION
 SELECT @.ID = IDNumber FROM IDNumberTable HOLDLOCK
 UPDATE IDNumberTable SET IDNumber = IDNumber + 1
 COMMIT
 END
INSERT INTO Test VALUES(@.ID, @.Col_SomeText)

Terri