July 24, 2002 at 8:57 pm
Hi,
I need to create the table below. The field "f1" must be auto incremented using a trigger. I cannot use IDENTITY because of some restrictions.
Where is the problem in my trigger?
-----------------------
create table TAB1
( f1 int primary key,
f2 varchar(20))
-----------------------
create trigger tr_t1 on TAB1
INSTEAD OF INSERT
AS
BEGIN
DECLARE @max-2 INT
SET @max-2 = 0
SELECT @max-2 = MAX(F1) FROM TAB1
UPDATE f
SET
i.f1 = @max-2
FROM
inserted i
END
Thanks in advance
July 25, 2002 at 12:16 am
Hi,
First of all the update statement in the trigger is incorrect.
You can't update the tables inserted or deleted.
What you are trying to do is also more complex than you would think.
The code you wrote is for insert statements for 1 statement. What if you have multiple inserts? (Then you have to use cursors?)
You have to give a value for f1 in the insert statement. A PK can't be NULL.
You also have to know what row to update. Otherwise you are going to update all the f1 fields in the TAB1 table.
Maybe it would be easier to use a function that returns the next PK value. And that you use that value with the insert statement?
The downside of this is that you have to use the function everywhere in you code...
Nico
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply