MS SQL 6.5 Identity columns and triggers

lsleeper

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

MS SQL 6.5 Identity columns and triggers

I'm converting to an MS SQL 6.5 database and I have a question about
identity fields.

Can an identity field be created with a trigger?

Rather than use a trigger, check if the key field is null, and fill in
the value selected from another table.

When I try this, the not null constraint kicks in before the trigger is
executed, as far as I can tell.

I want to be able to provide this value if the insert statement does
not.

Any help in this matter would be greatly appreciated.

Raymond J. Schapp

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Identity columns and triggers

An Identity field, by definition, cannot be filled in, the value is supplied
by SQL Server.
Instead, you can give the column an index and say that it is unique and then
fill in that field with your Trigger...

Why don't you just let SQL Server worry about assigning the value?

--Raymond

Quote

lsleeper wrote in message <363E21B9.52360...@mastertech.net>...
>I'm converting to an MS SQL 6.5 database and I have a question about
>identity fields.

>Can an identity field be created with a trigger?

>Rather than use a trigger, check if the key field is null, and fill in
>the value selected from another table.

>When I try this, the not null constraint kicks in before the trigger is
>executed, as far as I can tell.

>I want to be able to provide this value if the insert statement does
>not.

>Any help in this matter would be greatly appreciated.

lsleepe

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Identity columns and triggers

That is the question. How do I write a trigger to fill in that unique index
value. Since it can't be null it never gets to the trigger, unless I'm coding
it incorrectly.

I can't let SQL Server worry about it because I have to match a backend (SQL
Server) to an existing frontend written in Interbase. The front end needs to
continue to work for Interbase as well. Making these babies identity fields
would require changes on the front end and make the conversion more complicated.

Am I correct in assuming the key value being blank is causing the not null
constraint to fire before the trigger is executed? How do I trap the key being
null in the trigger before it tells me the value is blank?

Thanks for all your help.

Lawrence Sleeper
Masterpiece Technology, Inc.
Cary, NC, USA

Quote

Raymond J. Schappe wrote:
> An Identity field, by definition, cannot be filled in, the value is supplied
> by SQL Server.
> Instead, you can give the column an index and say that it is unique and then
> fill in that field with your Trigger...

> Why don't you just let SQL Server worry about assigning the value?

> --Raymond

> lsleeper wrote in message <363E21B9.52360...@mastertech.net>...
> >I'm converting to an MS SQL 6.5 database and I have a question about
> >identity fields.

> >Can an identity field be created with a trigger?

> >Rather than use a trigger, check if the key field is null, and fill in
> >the value selected from another table.

> >When I try this, the not null constraint kicks in before the trigger is
> >executed, as far as I can tell.

> >I want to be able to provide this value if the insert statement does
> >not.

> >Any help in this matter would be greatly appreciated.

Alan McDonal

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Identity columns and triggers

I imagine you want to do what I want to do i.e. make a trigger from a
generator in MSSQL just like we do so quicly and easily with Interbase. Well
I too am finding it difficult to get the trigger syntax correct. If you have
found a solution let me know
Alan

Quote

>That is the question. How do I write a trigger to fill in that unique
index
>value. Since it can't be null it never gets to the trigger, unless I'm
coding
>it incorrectly.

>I can't let SQL Server worry about it because I have to match a backend
(SQL
>Server) to an existing frontend written in Interbase. The front end needs
to
>continue to work for Interbase as well. Making these babies identity
fields
>would require changes on the front end and make the conversion more
complicated.

>Am I correct in assuming the key value being blank is causing the not null
>constraint to fire before the trigger is executed? How do I trap the key
being
>null in the trigger before it tells me the value is blank?

>Thanks for all your help.

>Lawrence Sleeper
>Masterpiece Technology, Inc.
>Cary, NC, USA

>Raymond J. Schappe wrote:

>> An Identity field, by definition, cannot be filled in, the value is
supplied
>> by SQL Server.
>> Instead, you can give the column an index and say that it is unique and
then
>> fill in that field with your Trigger...

>> Why don't you just let SQL Server worry about assigning the value?

>> --Raymond

>> lsleeper wrote in message <363E21B9.52360...@mastertech.net>...
>> >I'm converting to an MS SQL 6.5 database and I have a question about
>> >identity fields.

>> >Can an identity field be created with a trigger?

>> >Rather than use a trigger, check if the key field is null, and fill in
>> >the value selected from another table.

>> >When I try this, the not null constraint kicks in before the trigger is
>> >executed, as far as I can tell.

>> >I want to be able to provide this value if the insert statement does
>> >not.

>> >Any help in this matter would be greatly appreciated.

HBZhan

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Identity columns and triggers

Hi,
You should close TField.Required property:
MyDataset.FieldByName('IdentityField').Required := False;
This will prevent BDE from checking not null constraint.

Quote

lsleeper wrote in message <363F2EA3.2610E...@mastertech.net>...
>That is the question. How do I write a trigger to fill in that unique
index
>value. Since it can't be null it never gets to the trigger, unless I'm
coding
>it incorrectly.

HBZhang

dir..

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:MS SQL 6.5 Identity columns and triggers

IMHO, the reason of this problem is the way MSSQL manages the defined
constraints and fires the triggers.

Microsoft's docs explicitly states this : constraints are checked
first and, if they are succesfully verified, then the triggers will be
fired. (well, not exactly with these words).

Maybe you can consider using a stored proc to do this job. You can do
as many previous calculations before issuing you DML statement.

HTH.

Other Threads

1. Identity columns on SQL Server 6.5

2. MS SQL 6.5 and Identity fields

3. Identity Fields in MS-SQL 6.5 and / or 7.0

4. MS SQL 6.5 Triggers

5. Ms SQL 6.5 : Running External Program inside a Trigger

6. MS SQL 6.5 TimeStamp in update trigger

7. Dropping a column MS SQL 6.5

8. Non-blob column in table (MS SQL 6.5)

9. Dropping an Recreating a column in MS SQL 6.5

10. Decimal columns in MS Sql server 6.5