CODEWITHSUNDEEP
sql-serveroracle-databasetriggers
Rock
Rock

Reputation: 205

convert oracle trigger to ms sql trigger

I have an oracle trigger:

CREATE OR REPLACE TRIGGER  "BI_Info" 
BEFORE INSERT ON "Info" FOR EACH ROW
 WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) BEGIN
    SELECT NVL(MAX("Id")+1, 1) INTO :NEW."Id" FROM "Info";
END;

Now I want convert it to a ms sql trigger.

I know [Id] [int] IDENTITY(1,1) can implement this trigger similarly

but I do not want the Id auto add one.

Thanks.

Upvotes: 3

Views: 302

Answers (1)

peterm
peterm

Reputation: 92785

  1. MS SQL doesn't support BEFORE triggers. You should rather use INSTEAD OF triggers.
  2. MS SQL triggers are set-based rather than row-based triggers, so that should be taken into account

That being said you can try

CREATE TRIGGER tg_BI_Info_insert ON BI_Info
INSTEAD OF INSERT AS
BEGIN
  DECLARE @max INT

  SET @max = (SELECT COALESCE(MAX(id), 0) FROM BI_Info WITH (TABLOCKX, HOLDLOCK))

  INSERT INTO BI_Info (id, column1, ...)
  SELECT @max + ROW_NUMBER() OVER (ORDER BY (SELECT 1)), column1, ...
    FROM INSERTED
END

Here is SQLFiddle demo

If you want to emulate the part WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) allowing to insert explicitly assigned ids then

CREATE TRIGGER tg_BI_Info_insert ON BI_Info
INSTEAD OF INSERT AS
BEGIN
  DECLARE @max INT

  INSERT INTO BI_Info
  SELECT * 
    FROM INSERTED
   WHERE COALESCE(id, 0) <> 0

  SET @max = (SELECT COALESCE(MAX(id), 0) FROM BI_Info WITH (TABLOCKX, HOLDLOCK))

  INSERT INTO BI_Info (id, column1)
  SELECT @max + ROW_NUMBER() OVER (ORDER BY (SELECT 1)), column1
    FROM INSERTED
   WHERE COALESCE(id, 0) = 0
END

Here is SQLFiddle demo for that case

Upvotes: 2

Related Questions