locked
converting oracle compound trigger into sql server RRS feed

  • Question

  • I am trying to understand, table type option in sql server

    can you please help me with this conversion  

    and i could not find %ROWTYPE equivalent in sql server

    here is my PL/SQL

    COMPOUND TRIGGER

      TYPE t_sif_changes     IS TABLE OF TABLE_NAME%ROWTYPE INDEX BY SIMPLE_INTEGER;
      v_sif_changes            t_sif_changes;

      v_index                  SIMPLE_INTEGER       := 0;
      v_threshhold    CONSTANT SIMPLE_INTEGER       := 50; --maximum number of rows to write in one go.

          
    Tuesday, May 12, 2020 5:32 PM

All replies

  • Unfortunately SQL Server does not have datatype similar to %Rowtype in Oracle but we can achieve this via cursor

    here sample

    DECLARE @colA varchar(50), @colB varchar(50)
    
    DECLARE myCursor CURSOR FOR
    Select columnA, columnB From table
    
    OPEN myCursor
    
    FETCH NEXT FROM myCursor INTO @colA, @colB
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    --do something with @colA and @colB
    
    FETCH NEXT FROM myCursor INTO @colA, @colB
    
    END
    
    CLOSE myCursor
    DEALLOCATE myCursor


    http://uk.linkedin.com/in/ramjaddu

    • Proposed as answer by Lily Lii Thursday, May 14, 2020 8:09 AM
    Tuesday, May 12, 2020 5:58 PM
  • Hi Sreenigaddam,

    In SQL Server, you can't mimic or inherit a table definition using %TYPE and %ROWTYPE, there is something like DECLARE @local_variable.

    refer to: 

    %Rowtype equivalent in SQL Server.

    Convert Oracle trigger to SQL Server.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 13, 2020 3:15 AM
  • Hi Sreenigaddam,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 14, 2020 8:10 AM