Search code examples
sql-serveroracle-databasetriggerssql-server-2000

Convert Oracle trigger to SQL Server

I'm not familiar with MSSQL triggers I need to convert a Oracle trigger in to MSSQL

here's the Oracle trigger:-

create or replace trigger TRG_GRP_SEQ
before insert on T_USER_GROUP
for each row
begin
select count(*)  into :new.GROUPSEQUENCE from  T_USER_GROUP;
end;

I'm in trouble covering the before statements and for each need some helping hand.

Description: This trigger does is before inserting each row in to the table T_USER_GROUP the GROUPSEQUENCE will increment by one value determining the total count (just like ID generation)

Thanks.

Solution

  • SQL Server can make columns identity which means they autoincrement for newly inserted rows. I recommend you use one, which would let you dispense with the trigger entirely.

    If you already have data in your table, then you'll ned to create a new table and populate it like so:

    CREATE TABLE T_USER_GROUP_New (
   GroupSequence int identity(1,1) NOT NULL,
   <Other Columns ...>
);

SET IDENTITY_INSERT T_USER_GROUP_New ON;
INSERT T_USER_GROUP_New (GroupSequence, <OtherColumns ...>)
SELECT * FROM T_USER_GROUP;
SET IDENTITY_INSERT T_USER_GROUP_New OFF;

-- Add script here to drop FK constraints from any tables with an FK to T_USER_GROUP

EXEC sp_rename 'T_USER_GROUP', 'T_USER_GROUP_Old';
EXEC sp_rename 'T_USER_GROUP_New', 'T_USER_GROUP';

-- Add script here to recreate FK constraints from any tables with an FK to T_USER_GROUP

-- Add script here to add appropriate indexes and constraints to T_USER_GROUP
-- and rename or drop them from T_USER_GROUP_Old

    Now you can completely skip the GroupSequence column when inserting, and it will always get the next, incremented value. You can learn this value immediately after like so:

    DECLARE @NewGroupSequenceStart int,
   @NewGroupSequenceEnd int;

INSERT T_USER_GROUP (<Columns not including GroupSequence ...>)
VALUES (<Values ...>);
-- or SELECT <Columns ...> FROM Somewhere

SELECT @NewGroupSequenceStart = Scope_Identity(), @NewGroupSequenceEnd = @@RowCount;
SET @NewGroupSequenceEnd = @NewGroupSequenceEnd + @NewGroupSequenceStart - 1;

-- Now these two variables have the start and end GroupSequence IDs
-- that were newly generated (you can insert multiple rows).
-- This could probably be cleaned up a little but I don't have time to test now.