Ditutup

MS SQL Update Trigger to Sync Tables

This is a complex trigger. This will update mission-critical business values. I want to divide this into two jobs. Proof (small) and then Finished work. If you have the experience to take this project, let me know price for initial proof, and price for finished version. I can send scripts to create the tables.

Here is the table structure, trigger action, and initial proof of concept:

TABLE 1

SalesReport_Day (160 fields, with about 20 that are calculated fields)

Each row represents the total value for various metrics for a day and is updated multiple times an hour. I am only concerned with the first 11 fields for the test.

TABLE 2

SalesReport_Week (Is identical to SalesReport_Day with the exception that it has 2 date range fields (DateStart: first day of the week) and (DateEnd: last day of the week) instead of a {day of the month} field.

Each row represents the total value for the same metrics for a week. It is the aggregate of the daily values from SalesReport_Day for a week. It is not updated by code. It will be updated by a trigger only.

TRIGGER ACTION

Initial concept (wrong-will not work): When a value in Table1 (SalesReport_Day ) is updated, the trigger needs to update (increment) the same column in Table2 (SalesReport_Week) with the amount of the change. The problem is that Table2 does not know how much the value in Table1 was increase/decreased. It cannot just add the field value from Table1 to the corresponding field in Table2 because after the first update, it would be adding values already added.

Desired concept (I think this will work): Typically, only 1-3 fields in Table1 will be updated as a time. 1) The Table1 fields being updated and their original pre-update values need to be read to variables.

2) Then compare the new values to the pre-update values to get +- value (net change).

3) Then update Table2 with the net change values.

One other thing that adds slightly to the complexity is that the trigger must first check to see if a row for the week already exists in Table2 (SalesReport_Week). If so then update. If not them insert the new row with the new DateStart and DateEnd values. All fields are defined with default values to make the insert simple.

PROOF OF CONCEPT

I only need you to do the trigger for the first 11 fields just to confirm we can get it to work as expected.

FULL VERSION

Once the Proof of Concept is complete, I want to create the full working version which will update all non-calculated fields in SalesReport_Week AND an additional table,SalesReport_Month (Table 3), which is not part of the first test.

PURPOSE

1) Provide faster reporting and save resources by having data aggregated in the tables instead of a query.

2) Insure data across those tables representing multiple time frames is always in sync.

OTHER

I would be interested in your thoughts if using EntityFramework 6 vs SQL command would make any difference in the original code update that targets Table1. To be exact, I am wondering if the EF SQL output will make it clear which values are being updated (in creating the pre-update variables). Obviously, I don't want to read all 150+ fields into variables for each update. Just the ones being changed.

Kemahiran: Pengaturcaraan Pangkalan Data, Microsoft Access, Pelayan Microsoft SQL, MySQL, SQL

Lihat lagi: sql trigger audit tables, ms sql running projecty tables stored procedures, vba excel sql update, excel vbscript sql update, excel vba sql update sample, sas sql update, sql update xls file, need concept architecture project, sql update protocols, database error invalid sql update ads set credits, sql update running balance, sql update csv, net sql update syntax, sql creator trigger software, aspnet sql update record, write sql queries customer order tables, update multiple mysql tables php, add async update trigger, sql update flat files, problem access sql update

Tentang Majikan:
( 0 ulasan ) Waxahachie, United States

ID Projek: #15980574

30 pekerja bebas membida secara purata $165 untuk pekerjaan ini

hits

We have expertise and experience in MS SQL. I have read the requirement and am confident of delivering. Pl share sql scripts for both the tables with some sample data in it and MS SQL version. Relevant Skills and Exp Lagi

$250 USD dalam 3 hari
(739 Ulasan)
8.7
$210 USD dalam 3 hari
(580 Ulasan)
8.4
gopalvora

Hi i can work for MS SQL Update Trigger to Sync Tables So send me Private message at PMB so we can discuss more about it Thanks Relevant Skills and Experience i am expert at ,Database Programming,Microsoft Access,Mi Lagi

$135 USD dalam 4 hari
(232 Ulasan)
7.6
$147 USD dalam 3 hari
(85 Ulasan)
7.3
mpalgeo

Hi, the trigger can know the previous value , so we can get the prev val and find the difference from the new. Do you have only add and subtract ? if you have averages the things are different. Relevant Skills and Exp Lagi

$100 USD dalam 4 hari
(77 Ulasan)
6.4
codetod

Hi I am an SQL expert with 15 years of experience in Database Programming - writing and tuning queries, procedures and triggers Relevant Skills and Experience SQL DBA, Database Programming, Query Optimization, Trigger Lagi

$120 USD dalam 3 hari
(68 Ulasan)
6.3
feninsoftech

This task is to create a trigger that will update incremental values in the second table on an event in the first table. Relevant Skills and Experience I have 10 years experience in development and deployment that inc Lagi

$222 USD dalam 3 hari
(50 Ulasan)
6.6
schoudhary1553

Greeting, I have understood your MS SQL Update Trigger to Sync Tables task and can do it with your 100% satisfaction. Please ping me for more discussion. Relevant Skills and Experience I have more than 5 years of ex Lagi

$140 USD dalam 3 hari
(27 Ulasan)
6.0
jagdishbhatt38

I can provide you better solution by utilizing my knowledge and skills. Relevant Skills and Experience I am having more than 8 years of experience in IT industry.I have worked on .net(c#.net,vb.net,asp,asp.net,mvc) wi Lagi

$250 USD dalam 10 hari
(32 Ulasan)
5.2
MyAwesomeTeam

Nice to meet you, HUGInc. Do you need to divide this into two jobs and be read to variables ?.I can do this project as well We have experience in thoses skills: Database Programming, Microsoft Access, Lagi

$165 USD dalam 3 hari
(13 Ulasan)
4.8
AliSafder

I love challenging work... I can work on it let me know when I can start? Relevant Skills and Experience I have over 9 years of experience in Databases like SQL Server, Oracle, Microsoft Access and MySQL, Programming Lagi

$150 USD dalam 3 hari
(27 Ulasan)
4.6
dhanuitdevendra

We can help you with our Database Developer as a Service (DEVaaS). Please visit [login to view URL] for details. Relevant Skills and Experience Please have a look at our existing customer base (http://ww Lagi

$300 USD dalam 3 hari
(5 Ulasan)
4.3
khannanav

We are a team of Database experts with more than 12 years of experience in Database Management, modelling, application & programming. We have delivered projects to telecom, education, industries etc. Relevant Skills a Lagi

$222 USD dalam 3 hari
(11 Ulasan)
4.4
robbie2032

USA BASED - 10+ years combined web / software development experience. Let's get together and chat about this. I could get you up and running in no time Relevant Skills and Experience . Proposed Milestones $177 USD Lagi

$177 USD dalam 3 hari
(3 Ulasan)
4.2
sunbrek

This is simple to do. Can you tell me what database server are you using? Relevant Skills and Experience I'm an Informatics Engineer. C# (.NET), C++, Java Tutor at College and Unity3D (C#) Game Developer I have worked Lagi

$150 USD dalam 3 hari
(2 Ulasan)
4.0
talha48

Hi, Ready to work with you. We have gone through your proposal and surely can do the best work on it. Inbox me for further discussion. Relevant Skills and Experience We have good skills in MS SQL Server, Database Lagi

$200 USD dalam 6 hari
(1 Ulasan)
3.1
ghoshankur

1 Day job Quote-15$ Relevant Skills and Experience MS SQL Server, TSQL Proposed Milestones $155 USD - 1st Day-Trigger will be completed in MS SQL server 8

$155 USD dalam 3 hari
(0 Ulasan)
0.0
$111 USD dalam 3 hari
(0 Ulasan)
0.0
Sadav

Hi, I would like to propose a slightly different approach. I need the both table definition with some sample data. Please send me a message to discuss more about it. Relevant Skills and Experience More than 17 years Lagi

$143 USD dalam 3 hari
(0 Ulasan)
0.0
$155 USD dalam 3 hari
(0 Ulasan)
0.0