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

    Question

  • 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:

    Do I have to set something in SQL Server 2012 Express to allow multiples connexions?

    Thank you all for your time

    Claude, Québec, Canada


    Claude Larocque

    Tuesday, November 12, 2013 10:19 PM

Answers

  • 1. To enforce referential integrity  you would have create a trigger   or use OUTPUT column

    create table t1 ( i int identity not null primary key, j int not null unique )
    create table t2 ( i int not null, j int not null)

    insert into t1 (j)
    output inserted.i, inserted.j into t2
    select o.object_id from sys.objects as o

    select * from t1
    select * from t2
    drop table t1, t2;
    go

    2.Every user that attempts to create a order should get unique number? 

    http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking

    >>>Do I have to set something in SQL Server 2012 Express to allow multiples connexions?

    No


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 13, 2013 6:26 AM
  • 1. If you want to do it correctly, avoid using TRIGGERs unless there is no other option*.

    TRIGGERs are a hack. They hide code as they are executed indirectly, often contain convoluted logic (because of firing order), and cause all too many wasted hours determining "why did it do that?"

    If it is a set of steps that determine what data need be INSERTed, such as by default using the data from another TABLE, use a set of steps to INSERT the data. That is, instead of INSERTing INTO the TABLE directly, use a stored PROCEDURE** (SP). SPs contain any logic required and unlike a TRIGGER, it is obvious that it is being used and when it is used.

    2) That error seems like the a single ODBC connection is being used by two threads simultaneously. Make sure you create a second ODBC connection for use by the second thread.

    --

    * For example, when working with a vendor's databases that cannot be altered.

    ** Optionally, use of the SP over direct TABLE access can be enforced by CREATEing another USER, GRANTing EXECUTE ON the SP to the USER, but not GRANTing access to the TABLE directly. This forces the USER to use the SP. Many corporations use this excellent approach.



    Wednesday, November 13, 2013 11:24 AM

All replies

  • 1. To enforce referential integrity  you would have create a trigger   or use OUTPUT column

    create table t1 ( i int identity not null primary key, j int not null unique )
    create table t2 ( i int not null, j int not null)

    insert into t1 (j)
    output inserted.i, inserted.j into t2
    select o.object_id from sys.objects as o

    select * from t1
    select * from t2
    drop table t1, t2;
    go

    2.Every user that attempts to create a order should get unique number? 

    http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking

    >>>Do I have to set something in SQL Server 2012 Express to allow multiples connexions?

    No


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 13, 2013 6:26 AM
  • Instead of using a TRIGGER, you might also plan to have a VIEW that shows value of field "Société" from the table "Corporation".


    - Aalam | (Blog)

    Wednesday, November 13, 2013 6:46 AM
  • 1. If you want to do it correctly, avoid using TRIGGERs unless there is no other option*.

    TRIGGERs are a hack. They hide code as they are executed indirectly, often contain convoluted logic (because of firing order), and cause all too many wasted hours determining "why did it do that?"

    If it is a set of steps that determine what data need be INSERTed, such as by default using the data from another TABLE, use a set of steps to INSERT the data. That is, instead of INSERTing INTO the TABLE directly, use a stored PROCEDURE** (SP). SPs contain any logic required and unlike a TRIGGER, it is obvious that it is being used and when it is used.

    2) That error seems like the a single ODBC connection is being used by two threads simultaneously. Make sure you create a second ODBC connection for use by the second thread.

    --

    * For example, when working with a vendor's databases that cannot be altered.

    ** Optionally, use of the SP over direct TABLE access can be enforced by CREATEing another USER, GRANTing EXECUTE ON the SP to the USER, but not GRANTing access to the TABLE directly. This forces the USER to use the SP. Many corporations use this excellent approach.



    Wednesday, November 13, 2013 11:24 AM