Oracle to Sql Server conversion trigger

Question

I am new sql server I need to convert a trigger in sql server which i have in oracle, please help i tried it but failed.

Create or replace
Trigger AD_JOB_SCHEDULE BEFORE
    INSERT ON JOB_SCHEDULE FOR EACH row BEGIN
    if :new.ACCOUNT_SYSTEM_CODE is null
    Then
    :new.BATCH_TYPE:='POLICY_NIGHT';
    ELSEif :new.ACCOUNT_SYSTEM_CODE  is not null then
    :new.BATCH_TYPE:='ACCOUNT_NIGHT';
    END if;
END;

The above is my trigger.

BELOW is my attempt

 CREATE TRIGGER AD_JOB_SCHEDULE 
   ON  JOB_SCHEDULE 
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

    declare @value varchar(200)

    select @value=ACCOUNT_SYSTEM_CODE from inserted

    if @value is not null
    begin
        update JOB_SCHEDULE 
        set BATCH_TYPE='ACCOUNT_NIGHT'
        where ACCOUNT_SYSTEM_CODE is not null
    end
    else
    begin
        update JOB_SCHEDULE 
        set BATCH_TYPE='POLICY_NIGHT'
        where ACCOUNT_SYSTEM_CODE is  null
    end
END
GO

This is my complete code actually I just want to know that whether it will work or not as I don't have the scenario with me, I just have to write the code and deliver.

So, you tried. Where is your attempt? What errors did it generate or in what ways did it not work correctly? — Damien_The_Unbeliever, Oct 25 '17 at 06:42
What you get when you change `AFTER INSERT` with `FOR INSERT` ? — XING, Oct 25 '17 at 07:23
Yes u r right for is working while after is throwing error@Xing — raghav, Oct 25 '17 at 07:30
Very weird that this would happen as `FOR` and `AFTER` is exactly the same https://stackoverflow.com/questions/5340638/difference-between-a-for-and-after-triggers — Jaques, Dec 02 '19 at 12:48

Oracle to Sql Server conversion trigger

0 Answers0