CODEWITHSUNDEEP
sql-serveroracle-databasetriggers
Cleber
Cleber

Reputation: 11

Migrate trigger Oracle to SQL Server

People,

I need migrate a Oracle trigger to SQL server, but I could not do.

The trigger in Oracle is very simple:

CREATE OR REPLACE TRIGGER trigger_teste      
BEFORE INSERT OR UPDATE  
ON teste  
FOR EACH ROW  
DECLARE  
BEGIN  
:new.id      := (coalesce(:NEW.id,      0));  
:new.vlr_sal := (coalesce(:NEW.vlr_sal, 0.00));  
END;

I tried several ways but none successfully!

Thank for help!

Upvotes: 0

Views: 1198

Answers (2)

APC
APC

Reputation: 146219

This is not an appropriate use of triggers in any flavour of RDBMS. The SQL standard allows us to define default values when we create the table using the DEFAULT constraint syntax. Both Oracle and SQL Server have this.

Obviously you haven't do this when you created the table. The good news is we can use ALTER TABLE to add default constraints. Something like this 

alter table teste 
   alter column id set default 0

That's for SQL Server. In Oracle it would be:

alter table teste 
   modify id default 0

As the nameless equine points out, a complete replacement for the trigger must include NOT NULL constraints on the affected columns. If the existing table lacks not null constraints we can add them using the same syntax as shown above, replacing the DEFAULT clause with NOT NULL - or even combining the two clauses in the same statement.

Upvotes: 1

user330315
user330315

Reputation:

My T-SQL is a bit rusty, but something like this should work. Note that SQL server does not have row level triggers, only statement level triggers.

CREATE TRIGGER trigger_teste      
ON teste  
BEFORE INSERT OR UPDATE  
AS 
   update inserted
      set      id = coalesce(id, 0),
          vlr_sal = coalesce(vlr_sal, 0.0)
GO

(Not sure if I got missed a semicolon or not. I never understood when SQL Server needs or deosn't need one)

See the manual for more details:

http://msdn.microsoft.com/en-US/library/ms189799%28v=sql.90%29
http://msdn.microsoft.com/en-US/library/ms191300%28v=sql.90%29

Upvotes: 1

Related Questions