Trigger From Oracle to SQL SERVER

Question

Hi how can i rewrite this trigger from oracle to sql server?

CREATE OR REPLACE TRIGGER COUNTER
BEFORE INSERT OR UPDATE OF some_column ON my_table
FOR EACH ROW
BEGIN

   :NEW.My_counter := :NEW.My_counter+1;
   :NEW.value := :NEW.value+1;

END;

Thanks for the help.

Answer 1

CREATE TRIGGER trig_update
ON <table_name> or <database_name>
Instead of INSERT, UPDATE, DELETE /*made this instead of as I see your saying Before*/
AS
Being
<your sql code>
end;

Answer 2

Oracle is definitely more elegant in this case... Here is something you can try:

CREATE TRIGGER counter_trigger
   ON my_table AFTER INSERT, UPDATE
AS
BEGIN 
   update t
   set t.my_counter +=1,
       t.value += 1
   from my_table t
   where exists ( -- restrict to inserted or updated rows ...
      select null
      from inserted i
      where i.Id = t.Id
   ) and (exists ( -- ... where the specific column was updated
           select null
           from deleted d
           where d.Id = t.Id
           and d.some_column <> t.some_column -- add some form of coalesce here if column is nullable.
          )
          or not exists ( -- ... or the whole row was inserted.
           select null
           from deleted d
           where d.Id = t.Id
         )
   );
END
GO

The above makes the following assumptions. Where these are not true, you'll have to tweak the code a bit:

• Your table has a primary key column named Id.
• some_column is not nullable. If it is, adjust the condition to account for nulls.
• Your database has the recursive triggers enabled setting set to false. If you don't know about this setting, then you're probably ok.