rollback

Database
Enthusiast

rollback

Hi,

 

i have  SQL  code that i need convert to TERADATA , when i try  wrapped it  with Rollback i got Discrepancy between the out put on TD&SQL.

i think that i dont realy know how work the script of the Rollback on TERADATA.

for example i add part of my code:

 

declare @LastUpdateTime int = ISNULL((select convert(int,convert(varchar(100),(MAX(a.LastUpdateTime)),112)) from DWH.protocol_new_Snapshot_Table a),19000101)

                                         
if (@LastUpdateTime < convert(int,convert(varchar(100),(GETDATE()),112)))
begin

 begin try
 
  begin tran

  
  declare @DateToRun int = (select MAX(a.ContactDate_ID) from [DWH.Contacts (nolock) a where a.CampaignCode=4918 and a.ContactDate_ID >= convert(int,convert(varchar(100),(GETDATE()-5),112)))

 

  select aa.CID, aa.ChartName, aa.Cell_Name
  into #Cell_Names
  from (
    select a.*
    from #Got_Churn_Last_Time_temp a
    where a.GlobalContactType_ID in (1,25)  -- email,OMG
    ) aa
  where aa.CC_Rank=1

.........

............................

 ..............................................

........................................................

update a
  set a.IsInChurnProcess = 0,
   a.LastTimeExit = case when b.Exit_Reason='Active' then b.LastBetDate else GETDATE() end,
   a.LastExitReason = b.Exit_Reason
  from DWH.protocol_new_Snapshot_Table a
   inner join #Exit_Players b on a.cid=b.cid
  
  
  select CID
  from DWH.protocol_new_Snapshot_Table
   
  
  commit tran
  
 end try
 
 begin catch
  if @@trancount>0 rollback tran
 end catch

end

                            

 Thanks fo help!                                                   

3 REPLIES
Teradata Employee

Re: rollback

First, decide if you are going to run with Teradata proprietary transaction semantics or ANSI transaction semantics. (And you are defining a stored procedure, the session doing CREATE/REPLACE and the one doing the CALL must use the same semantics.)

 

In Teradata transaction mode: By default each request is implicitly committed if it succeeds (i.e. each SQL statement is a stand-alone transaction). You can prevent the implicit commit by wrapping a request or requests in BT (Begin Transaction) / ET (End Transaction) statements. If any request fails, it automatically triggers a rollback of the current transaction.

 

In ANSI transaction mode: Transactions implicitly start when a request is submitted, and end with an explicit COMMIT or ROLLBACK. If a request fails, that request is automatically rolled back but the transaction remains pending, i.e. any prior successful requests within that transaction are still uncommitted.

Enthusiast

Re: rollback

if i undarstand you currect if i choose Teradata mode i suppos to wrap BT&ET each DDL statemant im my Procedure?

 

Teradata Employee

Re: rollback

Definitely not. It makes no sense to wrap a single statement in BT/ET.

 

Check the Teradata SQL Reference manual SQL Request and Transaction Processing for more detail on how Teradata works.

 You might get more help if you showed the Teradata code and described what you want to happen versus what you observed.