Sql Trigger Problem

[blechner]

I'm trying to write a trigger for a database that will capture the previous value of a field and, on insert of data from the app, increment that field by 1.

This field is similar to a check number, and the first record in the database will not be 1, it will be something like 386651. I'd like to be able to grab that as my initial starting number and increment each record thereafter by 1.

I do have a record identifier set up that is separate from this value.

There will be a couple hundred records imported into this table before it goes live for data entry. It is at this point that the trigger will need to be in place to continue sequentially assigning a "document number" to these records.

So far, everything I've tried either errors out or starts at 1 regardless of whether or not I have select max() in the trigger.

Obviously I am just getting my feet wet with SQL triggers, but have had success creating them in the past. However, this has me stumped.

[ilearneditonline]

I'm trying to write a trigger for a database that will capture the previous value of a field and, on insert of data from the app, increment that field by 1.

This field is similar to a check number, and the first record in the database will not be 1, it will be something like 386651. I'd like to be able to grab that as my initial starting number and increment each record thereafter by 1.

I do have a record identifier set up that is separate from this value.

There will be a couple hundred records imported into this table before it goes live for data entry. It is at this point that the trigger will need to be in place to continue sequentially assigning a "document number" to these records.

So far, everything I've tried either errors out or starts at 1 regardless of whether or not I have select max() in the trigger.

Obviously I am just getting my feet wet with SQL triggers, but have had success creating them in the past. However, this has me stumped.

I would be most helpful if you provided your table schema and what you have tried. Is it not possible to make the field an IDENTITY field and have it increment by 1?

[ramesh1210]

I'm trying to write a trigger for a database that will capture the previous value of a field and, on insert of data from the app, increment that field by 1.

This field is similar to a check number, and the first record in the database will not be 1, it will be something like 386651. I'd like to be able to grab that as my initial starting number and increment each record thereafter by 1.

I do have a record identifier set up that is separate from this value.

There will be a couple hundred records imported into this table before it goes live for data entry. It is at this point that the trigger will need to be in place to continue sequentially assigning a "document number" to these records.

So far, everything I've tried either errors out or starts at 1 regardless of whether or not I have select max() in the trigger.

Obviously I am just getting my feet wet with SQL triggers, but have had success creating them in the past. However, this has me stumped.

I think that there is no need to create a trigger for achieving that functionality,
u are provided with an identity propertey in sql server,

CREATE TABLE exampleidentity_starts_from_386651_increments_by_1
(
id_num int IDENTITY(386651,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

[ck9663]

I think that there is no need to create a trigger for achieving that functionality,
u are provided with an identity propertey in sql server,

CREATE TABLE exampleidentity_starts_from_386651_increments_by_1
(
id_num int IDENTITY(386651,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

he's right. however, identity column ensures uniqueness but does not guarantees a fully sequential series. there could be hole on your sequence if the insert fails. also, you can not reuse a value once it's deleted.

[blechner]

The trigger I most recently tried is as follows:

CREATE TRIGGER [LW_NO] ON dbo.AP_LIEN
AFTER INSERT
AS

DECLARE @LW INT
SELECT @LW =MAX(LW_NUMBER)
FROM AP_LIEN
UPDATE AP_LIEN
SET LW_NUMBER = @LW + 1

The only problem is that this trigger updates the lw_number on all records, not just the one most recently inserted.

I don't think the create_table trigger would work because there are pre-existing records that will be imported into this table with lw_numbers already assigned.

This is why I need to be able to grab the previous lw_number, increment by 1, and assign this number to the record being inserted.

The table columns are as follows:

record_id uniqueidentifier
lw_number
sub_number
vendor_code
check_number
payment_amount
lw_date
lw_return_date

Of these data fields, all information is grabbed from our accounting SQL database except for the lw_number. The lw_number is currently assigned manually via an excel spreadsheet, which is VERY time consuming for our AP department. We may issue 175 - 200 lw's (lien waivers) with each check run.

Basically, all this table is doing is giving them a more efficient way to track lien waivers. To do this, I will be importing all lien waivers that are currently outstanding into the table, then all new lien waivers will be inserted after each check run.

[space1000]

try this one.

CREATE TRIGGER [LW_NO] ON dbo.AP_LIEN
AFTER INSERT
AS

DECLARE @LW INT
SELECT @LW =MAX(LW_NUMBER)
FROM AP_LIEN

UPDATE AP_LIEN
SET inserted.LW_NUMBER = @LW + 1

this way you only update the inserted record.

[blechner]

try this one.

CREATE TRIGGER [LW_NO] ON dbo.AP_LIEN
AFTER INSERT
AS

DECLARE @LW INT
SELECT @LW =MAX(LW_NUMBER)
FROM AP_LIEN

UPDATE AP_LIEN
SET inserted.LW_NUMBER = @LW + 1

this way you only update the inserted record.

Gave it a try but got the following error:
Error 1032: Cannot usethe column prefix "INSERTED". This must match the object in the update clause.

[blechner]

I found a workable solution.

After inserting all existing records into the table I changed the LW_NUMBER column to an identity column.

This allows future values in the LW_NUMBER column to be automatically assigned using the previous maximum value as the new starting value.

There could be gaps in the sequence if an insert fails, but for our purposes this is a non-issue.

[BalochDude]

A very simple example of how to write an sql trigger to increment a column on every insert:

http://www.chapterzero.co.uk/article...-triggers.aspx




You can also download the sql file from this page. I always find it easier if i have an sql file to work on rather than reading the code from the webpages.

Cheers!