Triggers implement field changes to the inserted data Oracle+sql Server

There has recently been a need to use triggers to make changes to a column of the inserted data, thus collating the trigger notation of Oracle and SQL Server for such requirements, and this article only mentions insert triggers.

First we create a table:

--Creating Test Table CREATE TABLE test (ID int primary key,name varchar), sex varchar (1), status int)

Our goal is to implement the requirement to set the status to 1 when the newly inserted data satisfies the sex field null, and to change the conditions according to the actual needs.

So SQL Server has the following syntax:

Create trigger [dbo]. [TRG1] on [dbo]. [Test] for insert as Declare C cursor to select Id,sex from inserteddeclare @id intdeclare @sex varchar (1) Open Cfetch NEX T from C to @id, @sexWhile @ @FETCH_STATUS = 0Beginif @sex is nullupdate dbo.test set Status=1 where [email protected]
   
    --This is updated with the primary key to maximize efficiency fetch NEXT from C to @id, @sexENDCLOSE cdeallocate C
   

Oracle's notation is:

Create or replace trigger TRG1 before insert on testreferencing new as new old as oldfor each rowbegin    if:old.sex is Null then    : new.status:=1;    End If; End Trg1;

You can also implement the same functionality.

Summary and comparison:

We found that because Oracle provides BEFORE/AFTER trigger notation so it's simpler to implement, and SQL Server relies on cursors only, it's clear that Oracle's approach is simpler and clearer.

However, it is not said that Oracle's syntax is better in all cases, and that sometimes SQL Server syntax can implement features that are difficult for Oracle triggers to implement.

Triggers implement field changes to the inserted data Oracle+sql Server