sql-server - 将 Oracle 触发器转换为 SQL Server

Question

我不熟悉 MSSQL 触发器，我需要将 Oracle 触发器转换为 MSSQL

这是 Oracle 触发器:-

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;

我在覆盖之前的陈述时遇到了麻烦，并且每个人都需要一些帮助。

说明:此触发器在将每一行插入表 T_USER_GROUP 之前执行 GROUPSEQUENCE 将增加一个确定总计数的值(就像 ID 生成一样)

谢谢。

Answer 1

SQL Server 可以制作列 identity这意味着它们为新插入的行自动递增。我建议你使用一个，这样你就可以完全不用扳机了。

如果您的表中已经有数据，那么您需要创建一个新表并像这样填充它:

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

现在，您可以在插入时完全跳过 GroupSequence 列，并且它将始终获得下一个递增的值。你可以像这样立即学习这个值:

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.