Equivalent of Oracle before insert trigger for each row in SQL Server

Question

I am facing a little bit different scenario. I have a table name test having primary key on the id column.

Previously in Oracle, I was using a before insert trigger to assign next sequence number whenever a null values(into id column) is attempted to insert.(working fine)

CREATE OR REPLACE TRIGGER schema.trigger_name 
BEFORE INSERT ON test FOR EACH ROW WHEN (new.id is null) 
BEGIN
    SELECT sequence.nextval INTO :new.id FROM dual; 
END;

Now I want to achieve the same in SQL Server (without altering the primary key).

Does this answer your question? [Sequence as default value for a column](https://stackoverflow.com/questions/14413383/sequence-as-default-value-for-a-column) — Charlieface, Apr 13 '21 at 19:23
By the way, I'm sure there is an excellent reason you are using an end-of-life-d version of SQL Server, you can only do `SEQUENCE` from 2012 upwards — Charlieface, Apr 13 '21 at 19:24
@Charlieface default values for a column only fire when there is no input provided for that column. In my case if I try to use this then it will through error as I have to handle null values in the id column. — Techno_Eagle, Apr 14 '21 at 07:16

score 0 · Accepted Answer · answered Apr 17 '21 at 07:42

Thank you so much guys for your contribution.

I have found the solution.

we can use

Instead off trigger to achieve this into SQL Server.

    CREATE OR ALTER TRIGGER schema_name.trigger_name
ON schema_name.table_name
INSTEAD OF INSERT
AS
BEGIN
Declare @loc_count int;
DECLARE @table_variable TABLE(LOC_ID INT);
set @loc_count=(select count(*) from inserted where inserted.id is null);

       Insert into schema_name.table_name
       select * from inserted where id is not null;

select * into #temp from inserted where id is null;


WHILE @loc_count > 0 
        BEGIN
             INSERT INTO @table_variable(LOC_ID)
             SELECT NEXT VALUE FOR schema.sequence_name;
        
        WITH  update_first_row AS
                (
                SELECT TOP 1 *
                FROM    #temp
                where id is null
                )
        update update_first_row
        set id=(SELECT LOC_ID from @table_variable)
        FROM  update_first_row;
        DELETE FROM @table_variable;
        set @loc_count=@loc_count-1;

END;
    INSERT INTO schema.table_name
    SELECT * FROM #TEMP;
    DROP TABLE #TEMP;
 
END;

I have just inserted all the rows having id as null into a temp table and then assigning sequence number to one row at a time without altering the column definition.

Equivalent of Oracle before insert trigger for each row in SQL Server

1 Answers1