RE: Update a date field with a Trigger when a transaction is posted to another table

jeremy 64107

SSC Enthusiast

Points: 112

May 11, 2014 at 4:01 am

#1712455

Ok ...

Customers

USE [EvaluationCompany7AU]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

Transheaders

SE [EvaluationCompany7AU]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF