Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Update a date field with a Trigger when a transaction is posted to another table Expand / Collapse
Author
Message
Posted Sunday, May 11, 2014 2:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
Eirikur Eiriksson (5/11/2014)
David Burrows (5/11/2014)
In that case use Eirikur's trigger although you could simplify it to
CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
AFTER INSERT, UPDATE
AS
IF UPDATE(TradingDate)
BEGIN
UPDATE c
SET c.ZLastDate = i.TradingDate
FROM inserted i
JOIN dbo.Customers c ON c.UniqueID = i.UniqueID
END



Slight difference in the logic, this code will do an update every time, even if the existing value is equal to the update value.


True and the only reason to restrict the update would be to reduce logging or prevent the regression of the date (ie update only if the date is greater than the current value)



Far away is close at hand in the images of elsewhere.

Anon.

Post #1569587
Posted Sunday, May 11, 2014 2:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:25 AM
Points: 14, Visits: 13
I'm using SQL Server 2008 R2.
Post #1569588
Posted Sunday, May 11, 2014 2:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 2,003, Visits: 5,474
jeremy 64107 (5/11/2014)
I'm using SQL Server 2008 R2.


Too bad my ESP (Extrasensory perception) connection is down The create table statements for both tables and the update statement sql code is needed at this point.

Check out this article on how to post a problem

Post #1569589
Posted Sunday, May 11, 2014 3:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:25 AM
Points: 14, Visits: 13
Sorry - I'm confused now and not following. Thanks anyway.
Post #1569590
Posted Sunday, May 11, 2014 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
jeremy 64107 (5/11/2014)
Sorry - I'm confused now and not following. Thanks anyway.


To try to figure out what the error you are getting we need the SQL used to create the tables so that we can see the column names and data types plus the SQL you used to insert/update the data.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1569595
Posted Sunday, May 11, 2014 3:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:25 AM
Points: 14, Visits: 13
I don't know what version of SQL the Programmers used to create those tables. Is there a way of finding out?
I'm using SQL Server 2008 R2 Express
Post #1569596
Posted Sunday, May 11, 2014 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:25 AM
Points: 14, Visits: 13
The transheaders table contains the TradingDate which is (datetime,null)
The Customers table contains the column ZLastSale which is (text,null)
Post #1569597
Posted Sunday, May 11, 2014 3:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
Use SQL Server Management Studio to connect to the database, find the database and tables in question. Right click on each table and select Script table as, CREATE, to new query window.
Copy and paste the results for each table here.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1569598
Posted Sunday, May 11, 2014 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:25 AM
Points: 14, Visits: 13
Ok ...

Customers

USE [EvaluationCompany7AU]
GO

/****** Object: Table [dbo].[CUSTOMERS] Script Date: 05/11/2014 20:00:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CUSTOMERS](
[UserLockNo] [int] NULL,
[CompanyID] [char](11) NOT NULL,
[UniqueID] [char](17) NOT NULL,
[UserFindNo] [int] NULL,
[CustomerCode] [char](16) NOT NULL,
[CustomerTitle] [char](50) NOT NULL,
[CustomerGroup] [char](20) NOT NULL,
[GroupNo] [int] NULL,
[MailingAddress] [char](200) NULL,
[MailingStreet1] [char](50) NULL,
[MailingStreet2] [char](50) NULL,
[MailingSuburb] [char](50) NULL,
[MailingState] [char](30) NULL,
[Postcode] [char](20) NULL,
[MailingCountry] [char](30) NULL,
[Category] [char](10) NULL,
[TaxStatus] [char](25) NULL,
[Notes] [text] NULL,
[BalanceOpening] [decimal](15, 4) NOT NULL,
[BalanceNext] [decimal](15, 4) NOT NULL,
[BalanceCurrent] [decimal](15, 4) NOT NULL,
[BalanceAge1] [decimal](15, 4) NOT NULL,
[BalanceAge2] [decimal](15, 4) NOT NULL,
[BalanceAge3] [decimal](15, 4) NOT NULL,
[BalanceAge4] [decimal](15, 4) NOT NULL,
[TotalDue] [decimal](15, 4) NOT NULL,
[OpeningDate] [datetime] NULL,
[DeliveryBasis] [char](3) NULL,
[LastReceiptAmt] [decimal](15, 4) NULL,
[LastReceiptDate] [datetime] NULL,
[OpenItem] [bit] NOT NULL,
[Remarks] [char](30) NULL,
[IsParentAc] [bit] NULL,
[BankName] [char](40) NULL,
[BranchName] [char](30) NULL,
[BankAccount] [char](30) NULL,
[HoldOrders] [bit] NULL,
[HoldDeliveries] [bit] NULL,
[HoldInvoices] [bit] NULL,
[CreditLimit] [decimal](15, 4) NULL,
[TaxNumber] [char](16) NULL,
[Interest] [decimal](15, 4) NOT NULL,
[DeliveryAddress] [char](200) NULL,
[DeliveryStreet1] [char](50) NULL,
[DeliveryStreet2] [char](50) NULL,
[DeliverySuburb] [char](50) NULL,
[DeliveryState] [char](30) NULL,
[DeliveryPostcode] [char](20) NULL,
[DeliveryCountry] [char](30) NULL,
[AnalysisNo] [int] NULL,
[AnalysisTitle] [char](20) NULL,
[ParentAccount] [char](11) NULL,
[Status] [char](4) NOT NULL,
[QuoteMethod] [char](1) NOT NULL,
[OrderMethod] [char](1) NOT NULL,
[DeliveryMethod] [char](1) NOT NULL,
[InvoiceMethod] [char](1) NOT NULL,
[StatementMethod] [char](1) NOT NULL,
[DocumentFolder] [char](128) NULL,
[Terms] [char](4) NOT NULL,
[Discount] [decimal](15, 4) NULL,
[CompanyFax] [char](25) NULL,
[CompanyPhone] [char](25) NULL,
[SalesContact] [char](50) NULL,
[AccountsContact] [char](50) NULL,
[AccountsEmail] [char](50) NULL,
[AccountManager] [char](20) NULL,
[CurrencyCode] [int] NOT NULL,
[CountryName] [char](24) NOT NULL,
[RunNumber] [int] NULL,
[DeliveryRunNo] [char](8) NULL,
[LastEditDateTime] [datetime] NULL,
[DeliveryRule] [char](4) NULL,
[BankSort] [char](30) NULL,
[NZParticulars] [char](12) NULL,
[NZAnalysis] [char](12) NULL,
[EFTReference] [char](20) NULL,
[LiquorNo] [char](15) NULL,
[ContractDate] [datetime] NULL,
[AlertEmail] [char](50) NULL,
[LimitAlert] [bit] NULL,
[TermsAlert] [bit] NULL,
[BranchID] [int] NULL,
[LocationNo] [int] NULL,
[EmailFolder] [text] NULL,
[PolicyID] [int] NULL,
[QuoteAction] [int] NULL,
[OrderAction] [int] NULL,
[SaleAction] [int] NULL,
[RecurringAction] [int] NULL,
[CreditAction] [int] NULL,
[KeywordsList] [char](255) NULL,
[EU_BIC] [char](11) NULL,
[EU_IBAN] [char](34) NULL,
[EU_TransType] [char](4) NULL,
[EU_MandateID] [char](35) NULL,
[EUMandateDate] [datetime] NULL,
[ZLastSale] [text] NULL,
CONSTRAINT [PK_CUSTOMERS_UniqueID] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Transheaders

SE [EvaluationCompany7AU]
GO

/****** Object: Table [dbo].[TRANSHEADERS] Script Date: 05/11/2014 20:01:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TRANSHEADERS](
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
[TransactionID] [char](25) NOT NULL,
[TransactionType] [char](2) NOT NULL,
[AccountID] [char](17) NOT NULL,
[DeliveryID] [char](17) NULL,
[HistoryID] [char](32) NOT NULL,
[EntryUser] [int] NOT NULL,
[TradingDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[OverDueDate1] [datetime] NULL,
[OverDueDate2] [datetime] NULL,
[OverDueDate3] [datetime] NULL,
[EntryDateTime] [datetime] NOT NULL,
[AccountingDate] [datetime] NOT NULL,
[PeriodNo] [int] NULL,
[TaxPeriod] [int] NULL,
[CurrentVersionNo] [int] NOT NULL,
[AccountingRef] [char](20) NULL,
[TradingRef] [char](20) NULL,
[OrderRef] [char](25) NULL,
[BatchNumber] [int] NOT NULL,
[Logged] [char](1) NOT NULL,
[Outstanding] [char](1) NOT NULL,
[Presented] [char](2) NULL,
[TempPresented] [char](2) NULL,
[HoldPayment] [bit] NULL,
[CountryCode] [int] NOT NULL,
[Decimals] [int] NULL,
[HedgeMethod] [char](1) NULL,
[Description] [char](70) NULL,
[ExtraText] [text] NULL,
[PayeeName] [char](50) NULL,
[BankAccountID] [char](17) NULL,
[AnalysisNo] [int] NULL,
[TaxCategory] [int] NULL,
[TaxCategory2] [int] NULL,
[InvoiceDiscount] [decimal](15, 4) NULL,
[WithholdingRate] [decimal](15, 4) NULL,
[Quantity] [decimal](15, 4) NULL,
[LocalCost] [decimal](15, 4) NOT NULL,
[LocalCharge] [decimal](15, 4) NULL,
[LocalGross] [decimal](15, 4) NOT NULL,
[LocalFreight] [decimal](15, 4) NULL,
[LocalWithheld] [decimal](15, 4) NOT NULL,
[LocalTax] [decimal](15, 4) NOT NULL,
[LocalTax2] [decimal](15, 4) NULL,
[LocalTax3] [decimal](15, 4) NULL,
[LocalDiscount] [decimal](15, 4) NOT NULL,
[LocalNet] [decimal](15, 4) NOT NULL,
[LocalOffset] [decimal](15, 4) NOT NULL,
[ForeignGross] [decimal](15, 4) NOT NULL,
[ForeignFreight] [decimal](15, 4) NULL,
[ForeignWithheld] [decimal](15, 4) NOT NULL,
[ForeignTax] [decimal](15, 4) NOT NULL,
[ForeignTax2] [decimal](15, 4) NULL,
[ForeignTax3] [decimal](15, 4) NULL,
[ForeignDiscount] [decimal](15, 4) NOT NULL,
[ForeignNet] [decimal](15, 4) NOT NULL,
[ForeignOffset] [decimal](15, 4) NOT NULL,
[ExchangeRate] [decimal](15, 4) NOT NULL,
[Unrealised] [decimal](15, 4) NULL,
[Realised] [decimal](15, 4) NULL,
[Notes] [text] NULL,
[NotesStatus] [int] NULL,
[PayMethod] [char](8) NULL,
[Bank_Expiry] [char](40) NULL,
[Branch_Authority] [char](30) NULL,
[BankAutomatic] [bit] NULL,
[BankNet] [decimal](15, 4) NULL,
[GrossValueTaxBox] [char](3) NULL,
[TaxAmountTaxBox] [char](3) NULL,
[DeliveryRunNo] [char](8) NULL,
[TotalWeight] [decimal](15, 4) NULL,
[QuoteExpiryDate] [datetime] NULL,
[Status] [char](8) NULL,
[StatementID] [int] NULL,
[PurchaseID] [char](25) NULL,
[Terms] [char](4) NULL,
[ProjectNo] [int] NULL,
[SourceID] [char](20) NULL,
[ZMSTRANTYE] [int] NULL,
[TaxCategory3] [int] NULL,
[CashType] [char](2) NULL,
[ContractID] [int] NULL,
[RCTActivityID] [int] NULL,
[TPARlogged] [bit] NULL,
CONSTRAINT [PK_TRANSHEADERS_TransactionID] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_TRANSHEADERS_UniqueID_UniqueID] UNIQUE NONCLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



Post #1569599
Posted Sunday, May 11, 2014 4:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
Your problem is CUSTOMERS UniqueID is char(17) and TRANSHEADERS UniqueID is int
SQL Server is trying to implicitly convert CUSTOMERS UniqueID to int to match against TRANSHEADERS UniqueID but at least one row in CUSTOMERS has a blank UniqueID



Far away is close at hand in the images of elsewhere.

Anon.

Post #1569601
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse