CODEWITHSUNDEEP
sql-servert-sqltriggers
Milan
Milan

Reputation: 3325

INSTEAD OF TRIGGER for unique PK ID

I have a table_1 which is getting data from table_2 through the following statement:

INSERT INTO database1.tbl_1 (idx, user_id, stasus_idx) 
    SELECT idx, user_id, stasus_idx  
    FROM database2.table_2 
    WHERE stasus_idx = 591

The table_1 has the exact column structure as table_2 except there is an extra column at the beginning to provide unique ID. So the table_1 columns look like this:

ID, idx, user_id, stasus_idx

I have created an INSTEAD OF TRIGGER like this:

CREATE TRIGGER trg_ID ON table_1
INSTEAD OF INSERT
AS
BEGIN
   SET IDENTITY_INSERT table_1 ON
   .
   . -- trigger
   .
   SET IDENTITY_INSERT table_1 OFF
END

....to insert the new unique ID for each row but id does not work as expected. It just inserts 1 record and its done. I could fetch all the rows into a CURSOR and process one by one but that would be terribly inefficient.

Thanks for help.

Upvotes: 0

Views: 147

Answers (2)

JodyT
JodyT

Reputation: 4412

If you are unable to change your database to make use of a sequence then you should be able to use your original query without the trigger like. Just add the SET IDENTITY_INSERT statements around your query.

SET IDENTITY_INSERT database1.tbl_1 ON;

INSERT INTO database1.tbl_1 (ID, idx, user_id, stasus_idx ) 
    SELECT ID, idx, user_id, stasus_idx  FROM database2.table_2 
    WHERE stasus_idx = 591;

SET IDENTITY_INSERT database1.tbl_1 OFF;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Perhaps a sequence solves your problem. You can easily define one, using the syntax:

create sequence sequence_name as int start with 1 increment by 1;

Note that the default type is bigint. And sequences seem to start at the smallest value rather than 1 by default.

You can then use these in tables, by using default. Here is an example:

create table t1 (
  id int primary key default (next value for test),
  id2 int identity unique,
  val int
);

Here is a simple SQL Fiddle illustrating them. The documentation I referred to in the comment seems quite useful.

Upvotes: 2

Related Questions