SQL transaction batch doesn't register some actions

Follow
Oops, something didn't work.

Try again?

Sign In to be begin to follow this thread

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.

Getting "follow" information

Stop following this thread

Start following this thread

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.

Removing your "follow"

Setting up your "follow"

Did you know you can
sign up for email notifications?
- Subscribe to this feed

reddit

Tweet

qwert231 M Kenyon

Jun 30, 2010 at 7:25 AM

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.
qwert231 M Kenyon

Jun 30, 2010 at 7:27 AM

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.
Oggelito continue;

Jun 30, 2010 at 7:39 AM

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...
qwert231 M Kenyon

Jun 30, 2010 at 7:52 AM

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
qwert231 M Kenyon

Jun 30, 2010 at 8:14 AM

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:

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
figuerres ???

Jun 30, 2010 at 11:56 AM

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 ?
qwert231 M Kenyon

Jun 30, 2010 at 12:17 PM

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.
figuerres ???

Jun 30, 2010 at 2:29 PM

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.
davewill here birdie birdie, get in my belly!

Jul 01, 2010 at 5:27 AM

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.
figuerres ???

Jul 01, 2010 at 12:16 PM

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!
qwert231 M Kenyon

Jul 06, 2010 at 10:42 AM

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?
Sven Groot Don't worry... I'm a doctor.

Jul 06, 2010 at 11:23 PM

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.
qwert231 M Kenyon

Jul 07, 2010 at 7:10 AM

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!!!?!)
qwert231 M Kenyon

Jul 13, 2010 at 10:59 AM

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!!!
davewill here birdie birdie, get in my belly!

Jul 14, 2010 at 11:06 AM

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?
Oggelito continue;

Jul 15, 2010 at 12:45 AM

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"
qwert231 M Kenyon

Jul 15, 2010 at 6:44 AM

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?
qwert231 M Kenyon

Jul 15, 2010 at 6:46 AM

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.

Thread Closed

This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.

SQL transaction batch doesn't register some actions

What does this mean?

What does this mean?

Thread Closed