Help with trigger...

[Maxxamillian]

Need to set a trigger so that when a new record is inserted the max value in column flx_objid will be taken and incrememnted by one for the new record. So far no luck. Here is what I am working with.

CREATE TRIGGER FLXTrans_ObjidCount
ON FLX_Transaction
FOR INSERT

AS

DECLARE @MaxCount int, @NewCount int, @OldCount int

SELECT @MaxCount = (Select Max(flx_objid) FROM FLX_Transaction)
SELECT @NewCount = @MaxCount + 1


IF @MaxCount is NULL
BEGIN
UPDATE FLX_Transaction SET flx_objid = 1
END
ELSE


UPDATE FLX_Transaction SET flx_objid = @NewCount

FROM

GO


Thanks in advance for your suggestions.

 

[foxdev]

Why not use an Identity column? It would be much, much simpler.

Also, in your code:

[tt]UPDATE FLX_Transaction SET flx_objid = @NewCount

FROM

GO[/tt]

the "FROM" would be an error. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[Maxxamillian]

Not quite sure what you mean by using an identity column. With the "from" removed from the code the trigger still will not fire.

 

[foxdev]

Look up Identity in Books Online.

An Identity column is a numeric (integer, usually) column that automatically increments itself with each new record. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[Maxxamillian]

Thanks a bunch for the tip!

 

[Maxxamillian]

Thanks a bunch for the tip!