Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Syntax differences Sybase - Oracle and MSSQL

    Hi, I have a Sybase script, that I really need to be running in a MSSQL enviroment. The part that is troublesome is as follows:

    CREATE TRIGGER CTI_VATCALC BEFORE INSERT ON cost_tran " & _
    " REFERENCING NEW AS new_record" & _
    " FOR EACH ROW " & _
    " BEGIN " & _
    " declare vat_var integer;" & _
    " SELECT vat INTO vat_var FROM cost_cat " & _
    " where cost_cat.cost_cat_id = new_record.cost_cat_id ;" & _
    " set new_record.amount_i_vat = new_record.amount_income*vat_var/100; " & _
    " set new_record.amount_e_vat = new_record.amount_expense*vat_var/100; " & _
    " set new_record.amount_i_tot = new_record.amount_income + new_record.amount_i_vat; " & _
    " set new_record.amount_e_tot = new_record.amount_expense + new_record.amount_e_vat; " & _
    " END"

    which is the sybase part.. now.. this is how the MSSQL version currently looks :

    CREATE TRIGGER CT_VATCALC ON cost_tran
    for insert as declare @vat integer
    BEGIN
    SELECT vat INTO vat FROM cost_cat
    WHERE cost_cat.cost_cat_id=:new.cost_cat_id
    :new.amount_i_vat:=:new.amount_income*vat/100
    :new.amount_e_vat:=new.amount_expense*vat/100
    :new.amount_i_tot:=new.amount_income + new.amount_i_vat
    :new.amount_e_tot:=new.amount_expense + new.amount_e_vat
    END

    But I am not sure how you update values in MSSQL. Can anyone help me with making this run for me?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First..the first part of the code looks like vb....I haven't worked with sysbase, but I thought it wouldn't look like that...

    Second part definetly looks like Oracle (new. and all)

    how about this

    Code:
    CREATE TRIGGER CT_VATCALC ON cost_tran
    FOR INSERT 
    AS 
    DECLARE @vat integer
    BEGIN
        SELECT vat 
          INTO vat 
          FROM cost_cat c
    INNER JOIN inserted i 
    	 ON c.cost_cat_id	=i.cost_cat_id
          WHERE amount_i_vat	=i.amount_income*vat/100
    	AND i.amount_e_vat	=i.amount_expense*vat/100
    	AND i.amount_i_tot	=i.amount_income + i.amount_i_vat
    	AND i.amount_e_tot	=i.amount_expense + i.amount_e_vat
    END
    But why declare the variable @vat?

    FYI..in Oracle, you don't have to reference the "new" inserted table, it's just available...very cool..

    In SQL Server it has to be part of the join...

    And as far as the "logic", I don't know what you're doing...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    thank you, I will try this

    Well, this is part of a script I will run once to make some new tables that will take values from tables with amongst other things expense information.

    What it does (the scripts that are made for other database engines) is to take the value and multiply it with tax and insert it into a new table.

    and you are right, the sybase example is modified to work with the scripting language in the application I am using this

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pk_hoff
    and you are right, the sybase example is modified to work with the scripting language in the application I am using this
    I don't know, but I think doing db admin outside the environment isn't a good idea..

    My Own Opinion....MOO

    But good luck, hope it works...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •