Porting triggers from Oracle. Conditional statements.

[REWT]

I am porting a database structure from Oracle to SQL Server 2000. I have managed to get all of the data and most of the constraints and triggers ported to the new database. However, I am uncertain of how to port a trigger with conditional statements in it to SQL Server 2000 from Oracle. I have the following statement in Oracle. I want to change the status of one field based on the values of two other fields, one which is the new value and the other is the value before the update. The Oracle trigger code follows. I currently have the trigger properly updating the MODIFY_DATE and MODIFY_BY fields. I just cannot find how to perform the conditional statement in SQL Server 2000. Thanks in advance for any assitance.

CREATE OR REPLACE TRIGGER "SCHEMA".BEF_UPDATE_TABLENAME
BEFORE UPDATE
ON TABLENAME
FOR EACH ROW
-- PL/SQL Block
DECLARE
USERVAR VARCHAR2(20);
BEGIN
SELECT USER INTO USERVAR FROM DUAL;
:NEW.MODIFY_DATE := SYSDATE;
:NEW.MODIFY_BY := USERVAR;
if :new.modify_date < '30-aug-2001' then
begin
if :new.REGISTRATION_STATUS = 'Cancel' then
:new.refund_paid := ld.price - 100;
end if;
end;
else
:new.refund_paid := '';
end if;

END ;

 

[SQLSister]

If statement is in T-SQL but it doesn't use a then
so it is:

If (Condition)
Do something here

Use begin and end around the statements to be executed if they are more than one line
You can also usse Else or else if to make actions happen for additional conditions.

 

[swampBoogie]

What is ld.price? A misspelled old.price? I assume that.
SQL server does not support row triggers so you need a statement trigger which does an update on the base table.


create trigger UPDATE_TABLENAME
on tablename for update
as
update t
set refund_paid =
case when i.registration_status = 'Cancel'
and i.modification_date < '30-aug-2001' then
t.price - 100 end,
modify_date = getdate(),
modify_by = session_user
from tablename t inner join inserted i
on t.pk = i.pk

Why the mix of datatypes in

:new.refund_paid := ld.price - 100;

:new.refund_paid := '';

?

 

[REWT]

Thanks for the help. I am not sure why there was a mix of data types. I had not designed the original database, and was instructed to do an exact copy in SQL server. the ld was a mis-typed old. Thanks again. Any online resources that might teach me more on the sql server 2000 capabilites, specifically database design, triggers, constraints and limitations.

 

[SQLSister]

Books Online is a great source as it comes with SQL server. Personally I prefer books to online sources. Oney ou might find helpful is SQL in a Nutshell by Kevin and Daniel Kline. It shows the differences in all the major SQL commands between several major databases including SQL Server and Oracle. Check the O'Reilly (

http://www.oreilly.com)site,

they might have and online version of it too.