Tech Off Thread

20 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL transaction batch doesn't register some actions

Back to Forum: Tech Off
  • User profile image
    qwert231

    I ran SQL Profiler while this VB6 app was running, to verify SQL was getting sent the correct information.

    We are changing a cash transaction in the software. The app generates a batch of sql statements, removing a previous cash transaction and replacing it with the new one. So, it touches 4 tables, rolling out the old transaction, and then touches those 4 again, putting in the new amounts.

     

    In profiler, I see all 8 statements occuring inside the sql transaction. But 2 of the changes don't always happen. The tables that are affected do have triggers, that replicate changes to a history table. I haven't seen any errors, but perhaps I'm not looking in the right spot.

     

    How can I troubleshoot why this is happening? The code runs, no errors. But the data hasn't been changed as it's supposed to be.

     

     

     

    More info:

    We have basically 4 tables, Account, Transmittal, Batch, and Transaction. Account get's the account's balance changed, and transmittal encompases a group of transactions, but has a balance as well. When we change a cash transaction, the amounts in Account and Transmittal are reduced by the previous transaction amount, and then increased by the new transactionsamount. The reduction does not happen, but the increase does. It's the same statement, but the first has a negative (-) amount, and the second has a positive (+) amount.

     

    The application is VB6, and the database was originally SQL 2000, but is now running in SQL 2005.

  • User profile image
    qwert231

    I'm not sure if this makes a difference, but even though the server is SQL 2005, the database is running in Compatibility level 'SQL Server 2000 (80)' mode.

  • User profile image
    Oggelito

    qwert231 said:

    I'm not sure if this makes a difference, but even though the server is SQL 2005, the database is running in Compatibility level 'SQL Server 2000 (80)' mode.

    Frankly, I think you have to do some more research yourself. Why not narrow it down to the specific statements thats acting weird? Put @@rowcount prints everywhere. I can't imagine whats wrong until I see that specific statement and trigger. Perhaps someone else can...

  • User profile image
    qwert231

    Oggelito said:
    qwert231 said:
    *snip*

    Frankly, I think you have to do some more research yourself. Why not narrow it down to the specific statements thats acting weird? Put @@rowcount prints everywhere. I can't imagine whats wrong until I see that specific statement and trigger. Perhaps someone else can...

    Thank you, appreciate the help. I'll start with the Acccount table, named ApplicantAgency (account of the customer, what we call an agent)

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Table [dbo].[ApplicantAgency]    Script Date: 06/30/2010 10:50:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ApplicantAgency](
     [AppAgencyID] [uniqueidentifier] NOT NULL,
     [AccountTypeID] [uniqueidentifier] NOT NULL,
     [ApplicationAgencyStatusID] [uniqueidentifier] NOT NULL,
     [AgentTypeID] [uniqueidentifier] NULL,
     [InactiveDate] [dbo].[Date] NULL,
     [BusinessName] [varchar](50) NULL,
     [AgentNumber] [char](8) NULL,
     [FirstName] [varchar](30) NULL,
     [MiddleName] [dbo].[MiddleName] NULL,
     [LastName] [varchar](30) NULL,
     [BusinessPhone] [char](18) NULL,
     [HomePhone] [dbo].[TelephoneNumber] NULL,
     [EmailAddress] [varchar](50) NULL,
     [SenateDistrict] [char](2) NULL,
     [HouseDistrict] [char](3) NULL,
     [County_PK] [smallint] NULL,
     [OverShortAmount] [decimal](9, 2) NULL,
     [AvailBondAmount] [decimal](9, 2) NULL,
     [NumberofDelinquencies] [smallint] NULL,
     [ApplicationFeeRcvdDate] [dbo].[Date] NULL,
     [ForwardedToRegionDate] [dbo].[Date] NULL,
     [SecondNoticeDate] [dbo].[Date] NULL,
     [FinalAppDispositionDate] [dbo].[Date] NULL,
     [GrandOpeningShipDate] [dbo].[Date] NULL,
     [LastPaymentDate] [dbo].[Date] NULL,
     [InitialShipDate] [dbo].[Date] NULL,
     [LastReportedDate] [dbo].[Date] NULL,
     [RejectionComment] [varchar](500) NULL,
     [StateParkOfficeIndicator] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_StateParkOfficeIndicator]  DEFAULT ((0)),
     [InternetAccount] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_InternetAccount]  DEFAULT ((0)),
     [PALSAgent] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_PALSAgent]  DEFAULT ((0)),
     [PALSTerminal] [int] NULL,
     [LastUpdatedBy] [varchar](50) NOT NULL,
     [LastUpdatedOn] [dbo].[LastUpdateOn] NOT NULL,
     CONSTRAINT [PK_ApplicantAgency] PRIMARY KEY NONCLUSTERED
    (
     [AppAgencyID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
     CONSTRAINT [UC_ApplicantAgency] UNIQUE CLUSTERED
    (
     [AgentNumber] ASC,
     [BusinessName] ASC,
     [BusinessPhone] ASC,
     [County_PK] ASC,
     [AccountTypeID] ASC,
     [ApplicationAgencyStatusID] ASC,
     [FinalAppDispositionDate] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_AccountTypeMaster] FOREIGN KEY([AccountTypeID])
    REFERENCES [dbo].[AccountTypeMaster] ([AccountTypeID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_AccountTypeMaster]
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_AgentTypeMaster] FOREIGN KEY([AgentTypeID])
    REFERENCES [dbo].[AgentTypeMaster] ([AgentTypeID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_AgentTypeMaster]
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_ApplicantAgencyStatusMaster] FOREIGN KEY([ApplicationAgencyStatusID])
    REFERENCES [dbo].[ApplicantAgencyStatusMaster] ([ApplicationAgencyStatusID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_ApplicantAgencyStatusMaster]

     


    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_InsUpd]    Script Date: 06/30/2010 10:50:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[tr_ApplicantAgency_InsUpd] on [dbo].[ApplicantAgency]
    AFTER INSERT, UPDATE AS
      UPDATE ApplicantAgency
      SET LastUpdatedOn = GETDATE()
      FROM ApplicantAgency,
              Inserted i
      WHERE
        ApplicantAgency.AppAgencyID = i.AppAgencyID

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_Upd_Hist]    Script Date: 06/30/2010 10:50:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
    INSTEAD OF UPDATE AS
      INSERT INTO ApplicantAgencyHistory
        (CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
      FROM Deleted d
     
      UPDATE ApplicantAgency
      SET AppAgencyID = i.AppAgencyID,
        AccountTypeID = i.AccountTypeID,
        ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
        AgentTypeID = i.AgentTypeID,
        InactiveDate = i.InactiveDate,
        BusinessName = i.BusinessName,
        AgentNumber = i.AgentNumber,
        FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName,
        BusinessPhone = i.BusinessPhone,
        HomePhone = i.HomePhone,
        EmailAddress = i.EmailAddress,
        SenateDistrict = i.SenateDistrict,
        HouseDistrict = i.HouseDistrict,
        County_PK = i.County_PK,
        OverShortAmount = i.OverShortAmount,
        AvailBondAmount = i.AvailBondAmount,
        NumberofDelinquencies = i.NumberofDelinquencies,
        ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
        ForwardedToRegionDate = i.ForwardedToRegionDate,
        SecondNoticeDate = i.SecondNoticeDate,
        FinalAppDispositionDate = i.FinalAppDispositionDate,
        GrandOpeningShipDate = i.GrandOpeningShipDate,
        LastPaymentDate = i.LastPaymentDate,
        InitialShipDate = i.InitialShipDate,
        LastReportedDate = i.LastReportedDate,
        RejectionComment = i.RejectionComment,
        StateParkOfficeIndicator = i.StateParkOfficeIndicator,
        InternetAccount = i.InternetAccount,
        PALSAgent = i.PALSAgent,
        PALSTerminal = i.PALSTerminal,
        LastUpdatedBy = i.LastUpdatedBy,
        LastUpdatedOn = i.LastUpdatedOn
      FROM Inserted i, ApplicantAgency
      WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

     

     

  • User profile image
    qwert231

    Oggelito said:
    qwert231 said:
    *snip*

    Frankly, I think you have to do some more research yourself. Why not narrow it down to the specific statements thats acting weird? Put @@rowcount prints everywhere. I can't imagine whats wrong until I see that specific statement and trigger. Perhaps someone else can...

    I don't want to overwhelm, but I feel, the more information the better. Here's a screen shot of the sql transaction as profiler sees it:

    profile screen

     

    The one that doesn't take is:

    exec proc_AdjustAgencyAmountsRmv 

    @AppAgencyID            = '{7085ABE9-0D13-45EC-8156-62E34FD94006}' ,

    @diffAvailBond        = -28  ,

    @diffOverShort        = -10  ,

    @LastUpdatedBy        = 'CAIAdminrmv'

     

    (diffAvailBond is the amount that needs to roll back)

     

    The proc looks like this:

    USE

    GO

    [AdminAppsTest]

    /****** Object: StoredProcedure [dbo].[proc_AdjustAgencyAmountsRmv] Script Date: 06/30/2010 11:13:49 ******/

    SET

    ANSI_NULLS ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: Mark Kenyon II

    -- Create date: 9/14/2009

    -- Description: Adjusts and Agency's Available Bond amount and Over Short Amount

    -- =============================================

    ALTER

    @AppAgencyID

    PROCEDURE [dbo].[proc_AdjustAgencyAmountsRmv] uniqueidentifier,

    @diffAvailBond

    money,

    @diffOverShort

    money,

    @LastUpdatedBy

    varchar(50),

    @LastReportDate

    datetime = NULL,

    @LastPaymentDate

    datetime = NULL

    AS

    BEGIN

     

    SET NOCOUNT ON;

    --print @LastReportDate

    --print @LastPaymentDate

     

     

    OverShortAmount

    LastUpdatedBy

    UPDATE ApplicantAgency SET AvailBondAmount = AvailBondAmount + @diffAvailBond, = OverShortAmount + @diffOverShort, = @LastUpdatedBy,

    LastPaymentDate

     

     

     

     

    LastReportedDate

     

     

     

     

     

    = CASE WHEN @LastPaymentDate IS NULL THEN LastPaymentDateELSE @LastPaymentDateEND, = CASE WHEN @LastReportDate IS NULL THEN LastReportedDateELSE @LastReportDateEND WHERE AppAgencyID = @AppAgencyID

    END

     

  • User profile image
    figuerres

    qwert231 said:

    I'm not sure if this makes a difference, but even though the server is SQL 2005, the database is running in Compatibility level 'SQL Server 2000 (80)' mode.

    silly question but do you have logic that fires off a rollback on an error in the sql code ?

    perhaps you are doing begins..... stuff ...... rollback.

    which leves the data as it was before.

     

    also i see a proc but i do not see a transaction ....

     

    a "Batch" is not a transaction so do you just mean that you have several statements that you are running ?

     

  • User profile image
    qwert231

    figuerres said:
    qwert231 said:
    *snip*

    silly question but do you have logic that fires off a rollback on an error in the sql code ?

    perhaps you are doing begins..... stuff ...... rollback.

    which leves the data as it was before.

     

    also i see a proc but i do not see a transaction ....

     

    a "Batch" is not a transaction so do you just mean that you have several statements that you are running ?

     

    In the code (VB6) I have this:

    Public gdbcnDatabase As ADODB.Connection

    gdbcnDatabase.BeginTrans

     

    strUpdateSQL = "proc_AdjustAgencyAmountsRmv ..." ' This contains the same SQL as proc_AdjustAgencyAmounts, but we pass the -xxx amount

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    strUpdateSQL = "... some other statement ... "

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    strUpdateSQL = exec proc_AdjustAgencyAmounts ... " ' Here we pass the +xxx amount

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    gdbcnDatabase.CommitTrans

     

    In SQL profiler I see:

    set implicit_transactions on

    exec proc_AdjustAgencyAmountsRmv  @AppAgencyID            = '{7085ABE9-0D13-45EC-8156-62E34FD94006}' , @diffAvailBond        = -28  , @diffOverShort        = -10  , @LastUpdatedBy        = 'CAIAdminrmv'

    ... other statements fired off ...

    exec proc_AdjustAgencyAmounts  @AppAgencyID            = '{7085ABE9-0D13-45EC-8156-62E34FD94006}' , @diffAvailBond        = 18  , @diffOverShort        = 0  , @LastUpdatedBy        = 'CAIAdmin' , @LastReportDate       = '2010-06-30 10:53:42' , @LastPaymentDate      = '2010-06-30 10:53:42' ;

    IF @@TRANCOUNT > 0 COMMIT TRAN

    set implicit_transactions off

     

    Therefore, I figured it was in an SQL transaction.

     

    At the end, the AvailBond amount should be 10 less (-28 + 18) that it was, but it's 18 more. Also, the diffOverShort isn't applied either.

     

    This code is several years old, and only started acting up when we switched from SQL 2000 to SQL 2005.

     

    This works sometimes. There are times I can run this, and the amount is 10 less when it's done, but not always.

     

    The original VB6 code did not use a stored procedure, but encapsulated all of the SQL statements as string values, including

    waitfor delay '00:00:00.005';

    in various sections.

     

    The function that generates the exec proc_AdjustAgencyAmounts string looks like this: (old code included, but commented out)

    Dim strSQL As String
       
    '    strSQL = "UPDATE ApplicantAgency "
    '    strSQL = strSQL & "SET "
    '    If curABAChangeAmount <> 0 Then
    '        strSQL = strSQL & "AvailBondAmount = AvailBondAmount + " & curABAChangeAmount & ", "
    '    End If
    '    If curOVAChangeAmount <> 0 Then
    '        strSQL = strSQL & "OverShortAmount = OverShortAmount + " & curOVAChangeAmount & ", "
    '    End If
    '    If strReportDate <> "" Then
    '        strSQL = strSQL & "LastReportedDate = '" & strReportDate & "', "
    '    End If
    '    If strPaymentDate <> "" Then
    '        strSQL = strSQL & "LastPaymentDate = '" & strPaymentDate & "', "
    '    End If
    '    strSQL = strSQL & "LastUpdatedBy = '" & gblUser & "' "
    '    strSQL = strSQL & "WHERE AppAgencyID = '" & strAgencyID & "'; "
        If desc = "rmv" Then
            strSQL = "proc_AdjustAgencyAmountsRmv "
        Else
            strSQL = "proc_AdjustAgencyAmounts "
        End If
        strSQL = strSQL & " @AppAgencyID            = '" & strAgencyID & "'"
        strSQL = strSQL & " , @diffAvailBond        = " & curABAChangeAmount & " "
        strSQL = strSQL & " , @diffOverShort        = " & curOVAChangeAmount & " "
        strSQL = strSQL & " , @LastUpdatedBy        = '" & gblUser & desc & "'"
        If strReportDate <> "" Then
            strSQL = strSQL & " , @LastReportDate       = '" & strReportDate & "'"
        End If
        If strPaymentDate <> "" Then
            strSQL = strSQL & " , @LastPaymentDate      = '" & strPaymentDate & "' ;" & vbCrLf
        End If
        CreateAppAgencySQL = strSQL

     

     

    The only reason I have two versions of the procedure, was because I wanted to differentiate in profiler what was being called. These are just some of the SQL statements that are getting sent between the gdbcnDatabase.BeginTrans and gdbcnDatabase.CommitTrans statements. I can see the data change per some of the statements, but not all of them. The proc_AdustAgencyAmounts proc updates ApplicantAgency, and I can see in the ApplicantAgencyHistory table the change for the second proc call, but not the first.

     

    Thank you for your continued help. This is old code, it has a lot of embedded SQL, and I'm still trying to grasp the business requirements (never documented of course) let alone the code design reasoning. Usually if I see a SQL statement showing up in profiler, it sticks in the database. I'm sure I'm missing something, but I'm not sure what.

     

     

  • User profile image
    figuerres

    qwert231 said:
    figuerres said:
    *snip*

    In the code (VB6) I have this:

    Public gdbcnDatabase As ADODB.Connection

    gdbcnDatabase.BeginTrans

     

    strUpdateSQL = "proc_AdjustAgencyAmountsRmv ..." ' This contains the same SQL as proc_AdjustAgencyAmounts, but we pass the -xxx amount

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    strUpdateSQL = "... some other statement ... "

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    strUpdateSQL = exec proc_AdjustAgencyAmounts ... " ' Here we pass the +xxx amount

    gdbcnDatabase.Execute strUpdateSQL

    strUpdateSQL = ""

     

    gdbcnDatabase.CommitTrans

     

    In SQL profiler I see:

    set implicit_transactions on

    exec proc_AdjustAgencyAmountsRmv  @AppAgencyID            = '{7085ABE9-0D13-45EC-8156-62E34FD94006}' , @diffAvailBond        = -28  , @diffOverShort        = -10  , @LastUpdatedBy        = 'CAIAdminrmv'

    ... other statements fired off ...

    exec proc_AdjustAgencyAmounts  @AppAgencyID            = '{7085ABE9-0D13-45EC-8156-62E34FD94006}' , @diffAvailBond        = 18  , @diffOverShort        = 0  , @LastUpdatedBy        = 'CAIAdmin' , @LastReportDate       = '2010-06-30 10:53:42' , @LastPaymentDate      = '2010-06-30 10:53:42' ;

    IF @@TRANCOUNT > 0 COMMIT TRAN

    set implicit_transactions off

     

    Therefore, I figured it was in an SQL transaction.

     

    At the end, the AvailBond amount should be 10 less (-28 + 18) that it was, but it's 18 more. Also, the diffOverShort isn't applied either.

     

    This code is several years old, and only started acting up when we switched from SQL 2000 to SQL 2005.

     

    This works sometimes. There are times I can run this, and the amount is 10 less when it's done, but not always.

     

    The original VB6 code did not use a stored procedure, but encapsulated all of the SQL statements as string values, including

    waitfor delay '00:00:00.005';

    in various sections.

     

    The function that generates the exec proc_AdjustAgencyAmounts string looks like this: (old code included, but commented out)

    Dim strSQL As String
       
    '    strSQL = "UPDATE ApplicantAgency "
    '    strSQL = strSQL & "SET "
    '    If curABAChangeAmount <> 0 Then
    '        strSQL = strSQL & "AvailBondAmount = AvailBondAmount + " & curABAChangeAmount & ", "
    '    End If
    '    If curOVAChangeAmount <> 0 Then
    '        strSQL = strSQL & "OverShortAmount = OverShortAmount + " & curOVAChangeAmount & ", "
    '    End If
    '    If strReportDate <> "" Then
    '        strSQL = strSQL & "LastReportedDate = '" & strReportDate & "', "
    '    End If
    '    If strPaymentDate <> "" Then
    '        strSQL = strSQL & "LastPaymentDate = '" & strPaymentDate & "', "
    '    End If
    '    strSQL = strSQL & "LastUpdatedBy = '" & gblUser & "' "
    '    strSQL = strSQL & "WHERE AppAgencyID = '" & strAgencyID & "'; "
        If desc = "rmv" Then
            strSQL = "proc_AdjustAgencyAmountsRmv "
        Else
            strSQL = "proc_AdjustAgencyAmounts "
        End If
        strSQL = strSQL & " @AppAgencyID            = '" & strAgencyID & "'"
        strSQL = strSQL & " , @diffAvailBond        = " & curABAChangeAmount & " "
        strSQL = strSQL & " , @diffOverShort        = " & curOVAChangeAmount & " "
        strSQL = strSQL & " , @LastUpdatedBy        = '" & gblUser & desc & "'"
        If strReportDate <> "" Then
            strSQL = strSQL & " , @LastReportDate       = '" & strReportDate & "'"
        End If
        If strPaymentDate <> "" Then
            strSQL = strSQL & " , @LastPaymentDate      = '" & strPaymentDate & "' ;" & vbCrLf
        End If
        CreateAppAgencySQL = strSQL

     

     

    The only reason I have two versions of the procedure, was because I wanted to differentiate in profiler what was being called. These are just some of the SQL statements that are getting sent between the gdbcnDatabase.BeginTrans and gdbcnDatabase.CommitTrans statements. I can see the data change per some of the statements, but not all of them. The proc_AdustAgencyAmounts proc updates ApplicantAgency, and I can see in the ApplicantAgencyHistory table the change for the second proc call, but not the first.

     

    Thank you for your continued help. This is old code, it has a lot of embedded SQL, and I'm still trying to grasp the business requirements (never documented of course) let alone the code design reasoning. Usually if I see a SQL statement showing up in profiler, it sticks in the database. I'm sure I'm missing something, but I'm not sure what.

     

     

    OK thats a lot clearer...

    I have honestly not done transactions that way the "implied transaction" thing that is.

     

    seems like it's possibly getting errors but you do not see them and leaving it messed up.

    if this must stay in VB6 then my first throught is to capture the whole process in terms of the SQL and make one sql exec out of the whole thing so you can use sql 2005's improved features to make this solid.

    ugly but just making one big string of sql statrments may be the way to go if this has to stay in VB6

    note that with sql 2005 you can do

     

    BEGIN TRAN

    BEGIN TRY

     

    ....

     

    CATCH

      ROLLBACK TRAN

     

    COMMIT TRAN

     

    so that any sql error uses a try catch to rollback the state.

     

    also i would add some testing to see if an update really did update the rows it was supposed to alter....

    if the code does not then a failed update can be really hosing the rest of the process.

     

    that and or can you make the whole thing a proc that takes params and has the tran inside the proc ?

    that would be cleaner to cal from VB6 and later from .Net if you move the app to it.

  • User profile image
    davewill

    qwert231 said:
    Oggelito said:
    *snip*

    Thank you, appreciate the help. I'll start with the Acccount table, named ApplicantAgency (account of the customer, what we call an agent)

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Table [dbo].[ApplicantAgency]    Script Date: 06/30/2010 10:50:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ApplicantAgency](
     [AppAgencyID] [uniqueidentifier] NOT NULL,
     [AccountTypeID] [uniqueidentifier] NOT NULL,
     [ApplicationAgencyStatusID] [uniqueidentifier] NOT NULL,
     [AgentTypeID] [uniqueidentifier] NULL,
     [InactiveDate] [dbo].[Date] NULL,
     [BusinessName] [varchar](50) NULL,
     [AgentNumber] [char](8) NULL,
     [FirstName] [varchar](30) NULL,
     [MiddleName] [dbo].[MiddleName] NULL,
     [LastName] [varchar](30) NULL,
     [BusinessPhone] [char](18) NULL,
     [HomePhone] [dbo].[TelephoneNumber] NULL,
     [EmailAddress] [varchar](50) NULL,
     [SenateDistrict] [char](2) NULL,
     [HouseDistrict] [char](3) NULL,
     [County_PK] [smallint] NULL,
     [OverShortAmount] [decimal](9, 2) NULL,
     [AvailBondAmount] [decimal](9, 2) NULL,
     [NumberofDelinquencies] [smallint] NULL,
     [ApplicationFeeRcvdDate] [dbo].[Date] NULL,
     [ForwardedToRegionDate] [dbo].[Date] NULL,
     [SecondNoticeDate] [dbo].[Date] NULL,
     [FinalAppDispositionDate] [dbo].[Date] NULL,
     [GrandOpeningShipDate] [dbo].[Date] NULL,
     [LastPaymentDate] [dbo].[Date] NULL,
     [InitialShipDate] [dbo].[Date] NULL,
     [LastReportedDate] [dbo].[Date] NULL,
     [RejectionComment] [varchar](500) NULL,
     [StateParkOfficeIndicator] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_StateParkOfficeIndicator]  DEFAULT ((0)),
     [InternetAccount] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_InternetAccount]  DEFAULT ((0)),
     [PALSAgent] [bit] NOT NULL CONSTRAINT [DF_ApplicantAgency_PALSAgent]  DEFAULT ((0)),
     [PALSTerminal] [int] NULL,
     [LastUpdatedBy] [varchar](50) NOT NULL,
     [LastUpdatedOn] [dbo].[LastUpdateOn] NOT NULL,
     CONSTRAINT [PK_ApplicantAgency] PRIMARY KEY NONCLUSTERED
    (
     [AppAgencyID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
     CONSTRAINT [UC_ApplicantAgency] UNIQUE CLUSTERED
    (
     [AgentNumber] ASC,
     [BusinessName] ASC,
     [BusinessPhone] ASC,
     [County_PK] ASC,
     [AccountTypeID] ASC,
     [ApplicationAgencyStatusID] ASC,
     [FinalAppDispositionDate] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_AccountTypeMaster] FOREIGN KEY([AccountTypeID])
    REFERENCES [dbo].[AccountTypeMaster] ([AccountTypeID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_AccountTypeMaster]
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_AgentTypeMaster] FOREIGN KEY([AgentTypeID])
    REFERENCES [dbo].[AgentTypeMaster] ([AgentTypeID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_AgentTypeMaster]
    GO
    ALTER TABLE [dbo].[ApplicantAgency]  WITH NOCHECK ADD  CONSTRAINT [FK_ApplicantAgency_ApplicantAgencyStatusMaster] FOREIGN KEY([ApplicationAgencyStatusID])
    REFERENCES [dbo].[ApplicantAgencyStatusMaster] ([ApplicationAgencyStatusID])
    GO
    ALTER TABLE [dbo].[ApplicantAgency] CHECK CONSTRAINT [FK_ApplicantAgency_ApplicantAgencyStatusMaster]

     


    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_InsUpd]    Script Date: 06/30/2010 10:50:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[tr_ApplicantAgency_InsUpd] on [dbo].[ApplicantAgency]
    AFTER INSERT, UPDATE AS
      UPDATE ApplicantAgency
      SET LastUpdatedOn = GETDATE()
      FROM ApplicantAgency,
              Inserted i
      WHERE
        ApplicantAgency.AppAgencyID = i.AppAgencyID

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_Upd_Hist]    Script Date: 06/30/2010 10:50:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
    INSTEAD OF UPDATE AS
      INSERT INTO ApplicantAgencyHistory
        (CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
      FROM Deleted d
     
      UPDATE ApplicantAgency
      SET AppAgencyID = i.AppAgencyID,
        AccountTypeID = i.AccountTypeID,
        ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
        AgentTypeID = i.AgentTypeID,
        InactiveDate = i.InactiveDate,
        BusinessName = i.BusinessName,
        AgentNumber = i.AgentNumber,
        FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName,
        BusinessPhone = i.BusinessPhone,
        HomePhone = i.HomePhone,
        EmailAddress = i.EmailAddress,
        SenateDistrict = i.SenateDistrict,
        HouseDistrict = i.HouseDistrict,
        County_PK = i.County_PK,
        OverShortAmount = i.OverShortAmount,
        AvailBondAmount = i.AvailBondAmount,
        NumberofDelinquencies = i.NumberofDelinquencies,
        ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
        ForwardedToRegionDate = i.ForwardedToRegionDate,
        SecondNoticeDate = i.SecondNoticeDate,
        FinalAppDispositionDate = i.FinalAppDispositionDate,
        GrandOpeningShipDate = i.GrandOpeningShipDate,
        LastPaymentDate = i.LastPaymentDate,
        InitialShipDate = i.InitialShipDate,
        LastReportedDate = i.LastReportedDate,
        RejectionComment = i.RejectionComment,
        StateParkOfficeIndicator = i.StateParkOfficeIndicator,
        InternetAccount = i.InternetAccount,
        PALSAgent = i.PALSAgent,
        PALSTerminal = i.PALSTerminal,
        LastUpdatedBy = i.LastUpdatedBy,
        LastUpdatedOn = i.LastUpdatedOn
      FROM Inserted i, ApplicantAgency
      WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

     

     

    I noticed that the "after update" trigger updates the same record that triggered the trigger.  Seems like that isn't a good thing.  Maybe that is causing an infinite loop in 2005 where it wasn't in 2000.  Or maybe it was in 2000 but the results were left but now are cleaned up better in 2005.

  • User profile image
    figuerres

    davewill said:
    qwert231 said:
    *snip*

    I noticed that the "after update" trigger updates the same record that triggered the trigger.  Seems like that isn't a good thing.  Maybe that is causing an infinite loop in 2005 where it wasn't in 2000.  Or maybe it was in 2000 but the results were left but now are cleaned up better in 2005.

    YOW!!!  thats a good one... might do some really funky things!

  • User profile image
    qwert231

    figuerres said:
    davewill said:
    *snip*

    YOW!!!  thats a good one... might do some really funky things!

    Could be... How could I change that to be more specific?

  • User profile image
    Sven Groot

    davewill said:
    qwert231 said:
    *snip*

    I noticed that the "after update" trigger updates the same record that triggered the trigger.  Seems like that isn't a good thing.  Maybe that is causing an infinite loop in 2005 where it wasn't in 2000.  Or maybe it was in 2000 but the results were left but now are cleaned up better in 2005.

    I'm not entirely sure about SQL 2005, but in 2008 a trigger will not fire recursively unless the RECURSIVE_TRIGGERS option is set on the database. So if that option isn't set (which I believe is the default) updating the table on which the trigger is defined from that trigger is perfectly safe.

  • User profile image
    qwert231

    Sven Groot said:
    davewill said:
    *snip*

    I'm not entirely sure about SQL 2005, but in 2008 a trigger will not fire recursively unless the RECURSIVE_TRIGGERS option is set on the database. So if that option isn't set (which I believe is the default) updating the table on which the trigger is defined from that trigger is perfectly safe.

    Well, how about a database running on SQL 2005 in SQL 2000 compatability mode?

     

    I think I'm going to try this in my trigger statement, just to be safe:

    DISABLE TRIGGER MyTriggerName ON MyTableName

     

    ... my trigger statement to update

     

    ENABLE TRIGGER MyTriggerName ON MyTableName

     

     

     

    (PS, why did I have to click Reply 3 times!!!?!)

  • User profile image
    qwert231

    qwert231 said:
    Sven Groot said:
    *snip*

    Well, how about a database running on SQL 2005 in SQL 2000 compatability mode?

     

    I think I'm going to try this in my trigger statement, just to be safe:

    DISABLE TRIGGER MyTriggerName ON MyTableName

     

    ... my trigger statement to update

     

    ENABLE TRIGGER MyTriggerName ON MyTableName

     

     

     

    (PS, why did I have to click Reply 3 times!!!?!)

    Changed my triggers:

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_InsUpd]    Script Date: 07/12/2010 12:34:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_InsUpd] on [dbo].[ApplicantAgency]
    INSTEAD OF INSERT AS
      INSERT INTO ApplicantAgency
        (AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, GETDATE()
      FROM Inserted i

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_Upd_Hist]    Script Date: 07/12/2010 12:35:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
    INSTEAD OF UPDATE AS
      INSERT INTO ApplicantAgencyHistory
        (CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
      FROM Deleted d
     
      UPDATE ApplicantAgency
      SET AppAgencyID = i.AppAgencyID,
        AccountTypeID = i.AccountTypeID,
        ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
        AgentTypeID = i.AgentTypeID,
        InactiveDate = i.InactiveDate,
        BusinessName = i.BusinessName,
        AgentNumber = i.AgentNumber,
        FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName,
        BusinessPhone = i.BusinessPhone,
        HomePhone = i.HomePhone,
        EmailAddress = i.EmailAddress,
        SenateDistrict = i.SenateDistrict,
        HouseDistrict = i.HouseDistrict,
        County_PK = i.County_PK,
        OverShortAmount = i.OverShortAmount,
        AvailBondAmount = i.AvailBondAmount,
        NumberofDelinquencies = i.NumberofDelinquencies,
        ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
        ForwardedToRegionDate = i.ForwardedToRegionDate,
        SecondNoticeDate = i.SecondNoticeDate,
        FinalAppDispositionDate = i.FinalAppDispositionDate,
        GrandOpeningShipDate = i.GrandOpeningShipDate,
        LastPaymentDate = i.LastPaymentDate,
        InitialShipDate = i.InitialShipDate,
        LastReportedDate = i.LastReportedDate,
        RejectionComment = i.RejectionComment,
        StateParkOfficeIndicator = i.StateParkOfficeIndicator,
        InternetAccount = i.InternetAccount,
        PALSAgent = i.PALSAgent,
        PALSTerminal = i.PALSTerminal,
        LastUpdatedBy = i.LastUpdatedBy,
        LastUpdatedOn = GETDATE()  --i.LastUpdatedOn
      FROM Inserted i, ApplicantAgency
      WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

    So, I get the current date/time, but I'm not triggering a trigger. (Although, Recurssive triggers was off for this database.)

     

    Still the same issue.

     

    I enter my data -> DB sends statements to update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... the subtraction doesn't go through, but the addition does!!!

  • User profile image
    davewill

    qwert231 said:
    qwert231 said:
    *snip*

    Changed my triggers:

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_InsUpd]    Script Date: 07/12/2010 12:34:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_InsUpd] on [dbo].[ApplicantAgency]
    INSTEAD OF INSERT AS
      INSERT INTO ApplicantAgency
        (AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, GETDATE()
      FROM Inserted i

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_Upd_Hist]    Script Date: 07/12/2010 12:35:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
    INSTEAD OF UPDATE AS
      INSERT INTO ApplicantAgencyHistory
        (CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
      FROM Deleted d
     
      UPDATE ApplicantAgency
      SET AppAgencyID = i.AppAgencyID,
        AccountTypeID = i.AccountTypeID,
        ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
        AgentTypeID = i.AgentTypeID,
        InactiveDate = i.InactiveDate,
        BusinessName = i.BusinessName,
        AgentNumber = i.AgentNumber,
        FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName,
        BusinessPhone = i.BusinessPhone,
        HomePhone = i.HomePhone,
        EmailAddress = i.EmailAddress,
        SenateDistrict = i.SenateDistrict,
        HouseDistrict = i.HouseDistrict,
        County_PK = i.County_PK,
        OverShortAmount = i.OverShortAmount,
        AvailBondAmount = i.AvailBondAmount,
        NumberofDelinquencies = i.NumberofDelinquencies,
        ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
        ForwardedToRegionDate = i.ForwardedToRegionDate,
        SecondNoticeDate = i.SecondNoticeDate,
        FinalAppDispositionDate = i.FinalAppDispositionDate,
        GrandOpeningShipDate = i.GrandOpeningShipDate,
        LastPaymentDate = i.LastPaymentDate,
        InitialShipDate = i.InitialShipDate,
        LastReportedDate = i.LastReportedDate,
        RejectionComment = i.RejectionComment,
        StateParkOfficeIndicator = i.StateParkOfficeIndicator,
        InternetAccount = i.InternetAccount,
        PALSAgent = i.PALSAgent,
        PALSTerminal = i.PALSTerminal,
        LastUpdatedBy = i.LastUpdatedBy,
        LastUpdatedOn = GETDATE()  --i.LastUpdatedOn
      FROM Inserted i, ApplicantAgency
      WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

    So, I get the current date/time, but I'm not triggering a trigger. (Although, Recurssive triggers was off for this database.)

     

    Still the same issue.

     

    I enter my data -> DB sends statements to update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... the subtraction doesn't go through, but the addition does!!!

    There is something else going on.  You described "change my data" 2 times with the exact same set of steps.  In one it works.  In the second it doesn't work.  My assumption being that the same triggers are fired in the same sequence for both and therefore (being a computer) the result should be the same.

     

    As I look more at the trigger I'm wondering why.  So I'll just ask.  Why not simplify the trigger to simply write ApplicantAgencyHistory after Insert, Update and then pass the GetDate() for ApplicantAgency.LastUpdatedOn in the original update sql?

  • User profile image
    Oggelito

    davewill said:
    qwert231 said:
    *snip*

    There is something else going on.  You described "change my data" 2 times with the exact same set of steps.  In one it works.  In the second it doesn't work.  My assumption being that the same triggers are fired in the same sequence for both and therefore (being a computer) the result should be the same.

     

    As I look more at the trigger I'm wondering why.  So I'll just ask.  Why not simplify the trigger to simply write ApplicantAgencyHistory after Insert, Update and then pass the GetDate() for ApplicantAgency.LastUpdatedOn in the original update sql?

    I agree with davewill. Simplify the trigger, set ApplicantAgency.LastUpdatedOn after the actual insert or use GetDate() as input value depending on your choice. And do the ApplicantAgencyHistory insert in the same after insert/update trigger. You have a nice scenario in four steps, which will serve as a excellent "unit test"

  • User profile image
    qwert231

    davewill said:
    qwert231 said:
    *snip*

    There is something else going on.  You described "change my data" 2 times with the exact same set of steps.  In one it works.  In the second it doesn't work.  My assumption being that the same triggers are fired in the same sequence for both and therefore (being a computer) the result should be the same.

     

    As I look more at the trigger I'm wondering why.  So I'll just ask.  Why not simplify the trigger to simply write ApplicantAgencyHistory after Insert, Update and then pass the GetDate() for ApplicantAgency.LastUpdatedOn in the original update sql?

    Hmm... okay... so are you saying my UPDATE trigger is firing my INSERT trigger?

  • User profile image
    qwert231

    qwert231 said:
    qwert231 said:
    *snip*

    Changed my triggers:

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_InsUpd]    Script Date: 07/12/2010 12:34:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_InsUpd] on [dbo].[ApplicantAgency]
    INSTEAD OF INSERT AS
      INSERT INTO ApplicantAgency
        (AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, GETDATE()
      FROM Inserted i

     

    USE [AdminAppsTest]
    GO
    /****** Object:  Trigger [dbo].[tr_ApplicantAgency_Upd_Hist]    Script Date: 07/12/2010 12:35:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
    INSTEAD OF UPDATE AS
      INSERT INTO ApplicantAgencyHistory
        (CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
      SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
            InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
            HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
            AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
            SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
            InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
            PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
      FROM Deleted d
     
      UPDATE ApplicantAgency
      SET AppAgencyID = i.AppAgencyID,
        AccountTypeID = i.AccountTypeID,
        ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
        AgentTypeID = i.AgentTypeID,
        InactiveDate = i.InactiveDate,
        BusinessName = i.BusinessName,
        AgentNumber = i.AgentNumber,
        FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName,
        BusinessPhone = i.BusinessPhone,
        HomePhone = i.HomePhone,
        EmailAddress = i.EmailAddress,
        SenateDistrict = i.SenateDistrict,
        HouseDistrict = i.HouseDistrict,
        County_PK = i.County_PK,
        OverShortAmount = i.OverShortAmount,
        AvailBondAmount = i.AvailBondAmount,
        NumberofDelinquencies = i.NumberofDelinquencies,
        ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
        ForwardedToRegionDate = i.ForwardedToRegionDate,
        SecondNoticeDate = i.SecondNoticeDate,
        FinalAppDispositionDate = i.FinalAppDispositionDate,
        GrandOpeningShipDate = i.GrandOpeningShipDate,
        LastPaymentDate = i.LastPaymentDate,
        InitialShipDate = i.InitialShipDate,
        LastReportedDate = i.LastReportedDate,
        RejectionComment = i.RejectionComment,
        StateParkOfficeIndicator = i.StateParkOfficeIndicator,
        InternetAccount = i.InternetAccount,
        PALSAgent = i.PALSAgent,
        PALSTerminal = i.PALSTerminal,
        LastUpdatedBy = i.LastUpdatedBy,
        LastUpdatedOn = GETDATE()  --i.LastUpdatedOn
      FROM Inserted i, ApplicantAgency
      WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

    So, I get the current date/time, but I'm not triggering a trigger. (Although, Recurssive triggers was off for this database.)

     

    Still the same issue.

     

    I enter my data -> DB sends statements to update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... it works

    I change my data -> DB sends statements to update Transmittal row (-$$amount) and Agency row (-$$ amount) then again update Transmittal row (+$$amount) and Agency row (+$$ amount) ... the subtraction doesn't go through, but the addition does!!!

    Also, It seems that if I call the procedure that does the update to remove the previous amount, I won't be able to do that again for some time. Profiler will show that the procedure is called, but I won't see it actually change the data for a while. After a half hour or so, it would work again.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.