Solved

Set a column in a table to get the default value from a column in another table

Posted on 2013-11-12
13
258 Views
Last Modified: 2013-11-27
Good evening,

After Learning Microsoft Access and VBA for the past 3 years, now I have decided to link via ODBC to an SQL server database for better performance. I am new to SQL and I have a strong desire to learn it quite fast...

I have 2 questions:

First: I want a field named "Société" in a table named "FactureMERCURYToutes" to have as a default value, the field "Société" from the table "Corporation"

I read that a trigger could be best way to achieve that, can someone give me how the fields mentioned above could be written, it does not need to be a trigger.


Second: My first goal when I have decided to link to an SQL server database is that many of my customers which to have 3 to 10 users to create orders simultaneoulsy, Access security is not as stong as an SQL database also.

However, I try to connect 2 computers and create 2 orders at the same time and I received this message:

Message when trying to do 2 orders at the same time from 2 different computers
Do I have to set something in SQL Server 2012 Express to allow multiples connexions?


Thank you all for your time
0
Comment
Question by:supportAutoCaisse
  • 7
  • 6
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39643709
Although you can certainly replicate the column value in another table with a trigger, maybe you can explain why you need that.  Generally it is poor design and wasteful to duplicate data in two places.

How are you creating the orders?  Are you using a stored procedure?  Yes there is a maximum number of connections property for an instance that can be adjusted but typically defaults to 0 (unlimited).
0
 

Author Comment

by:supportAutoCaisse
ID: 39643728
Hi BriCrowe,
In my application, I am using a debit and credit card processing payments system and this requires to have many tables with duplicate information.
I agree with you that it may seems poor design, but to be able to pass the PCI compliance I was obligated to do so.

So in these tables, I have the same information that are required, in my question, if you can provide me with the trigger on how to have as default the field "Société" from the table "Corporation" in my table "FactureMercuryToutes", I then would be able to use this trigger to assign that default value to many other tables.

Thanks, hopefully this is clear enough..
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39645002
How are the two tables joined?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39645031
Change the "ID" columns in the join to the appropriate column name:

IF OBJECT_ID('trg_Corporation_UpdateSociete', N'TR') IS NOT NULL
	DROP TRIGGER trg_Corporation_UpdateSociete;
GO

CREATE TRIGGER trg_Corporation_UpdateSociete
ON Corporation
AFTER UPDATE, INSERT
AS

SET NOCOUNT ON;

IF UPDATE(Societe)
BEGIN
	UPDATE FactureMERCURYToutes
	SET Societe = I.Societe
	FROM INSERTED AS I
	INNER JOIN FactureMERCURYToutes AS FMT
		ON I.ID = FMT.ID
	WHERE I.Societe <> FMT.Societe
END

Open in new window

0
 

Author Comment

by:supportAutoCaisse
ID: 39646507
Hi BriCrowe,
Thank you for your post, now that Ihave successfully created that trigger, how can I start that trigger on the default value?, please keep in mind that I am new to SQL and still trying to figure out the procédures.

If I go to FactureMercuryToutes design, I go to the column where I want the trigger to be active??, do I put the name of that trigger somewhere? if yes, where?

For someone with experience like yours might be a bizarre question, but I will learn because of people like yourself that takes some times to help

Claude
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39646515
The trigger will only help you with inserts/updates after it is created.  If you want to fill the value for past records than just do an update.

UPDATE FactureMERCURYToutes
SET Societe = C.Societe
FROM Corporation AS C
INNER JOIN FactureMERCURYToutes AS FMT
      ON C.ID = FMT.ID
WHERE C.Societe <> FMT.Societe
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:supportAutoCaisse
ID: 39646795
Thanks again,
I want to use the trigger when I am creating a new record in the FactureMercuryToutes table from Microsoft Access.

Please take note that I can create a record by VBA code in that table, the ID comes after the record has been created, so I would like the Société column to update at the same time, so far I don't know where to tell the trigger to act, does it make any sense?

For example in the design of that table in the field "Société", if I put a default value of ('Auto-Caisse'), when the record is created
I can see Auto-Caisse, I would like to see the value of the field "Société" from the table "Corporation".
I have created the trigger, but I do not know where to put the command to execute that trigger when I create a new record, the result is that I don't see the desire field from the Corporation table.

We are going to get there.
Note: When I will finally get it, I have around 10 tables with 5 fields each to create triggers, so you can see the importance of getting it :)

Have a great day!
Claude
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39646879
The trigger will fire automatically whenever there is an insert or update to the Corporation table regardless of the source.  It wouldn't matter if the change comes from Access, .Net Code, or from a TSQL query in SSMS.

You can test it by doing an insert or update on Corporation like...

UPDATE Corporation
SET societe = 'test'
WHERE ID = <some value>

SELECT societe
FROM FactureMERCURYToutes
WHERE ID = <some value>

You should see the 'test' value in FactureMERCURYToutes after the update.
0
 

Author Comment

by:supportAutoCaisse
ID: 39647682
Hi BriCrowe,
You are right, I did see the test into FactureMERCURYToutes after the update, however, in VBA I will so something like this:

set societe = Corporation.Societé
and then, when a new record will be created, I will have the value of the field societe from the corporation table into the FactureMERCURYToutes's societe field

Thanks
Claude
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39648209
I'm not following that last part.  What is your concern in the VBA code?
0
 

Author Comment

by:supportAutoCaisse
ID: 39649527
Hi BriCrowe,
This is not a concern, just that all my queries, forms, reports, macros and modules are in a Microsoft Access 2013 format, only the tables are linked to the SQL Server Express 2012, so in Access, I can easily program the tables FactureMERCURYToutes to have its column Société to get the value from the table Corporation.Société.

Because I am not familiar with SQL server, I try to figure out that when a record will be created, the value of another table will be the defaut.

Thanks
Claude
0
 

Author Closing Comment

by:supportAutoCaisse
ID: 39680627
Many users told me that triggers are to be used only when nothing else works; that this is not a good way of changing data, may be because they don't know as much as you do, anyway I am closing this topic and hopefully figure out a solution that will fit my needs more popular.
Thanks anyway
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39681321
I try to avoid triggers if possible but they are a legitimate tool.  There are a couple of problems with triggers.  First, they are not very visible which makes it easy to forget that the functionality is there.  Second, if you are not careful and allow triggers to proliferate than it is easy to get into a circular update situation where tables are updating each other in an endless cycle.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now