Constraints against an external SQL server table

[RogerCooper]

I need to create a constraint within SQL Server that enforces the rule NET_WEIGHT <= GROSS_WEIGHT. My problem is that the GROSS_WEIGHT field is in a different SQL server database. How would I create such a constraint? Is there a way I could create a view or some similar object within my database which would have the data from the other database? Or is there a way I can define a constraint to lookup the data directly from the other database?

 

[Ranman256]

I have checks in the entry form to make sure the user has entered correct data.
Done on the form for easy changes, rather than have to edit the table properties.

If IsValidForm() then
SaveData
Docmd.Close
endif

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(txtFile)
      vMsg = "File is missing"
   Case IsNull(txtTblData)
      vMsg = "Table name is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

 

[Josef P.]

You could use an insert trigger in the SQL server to raise an error if necessary.

 

[Minty]

Can you link the other table from the other database in Access?
Realistically, if that data is important in your database it should really be available as data in your database.

 

[RogerCooper]

I will check out using an insert trigger.

I link to the data using Access, but most users need to go into it using our ERP, so it is better to deal with it at a database level.

 

[Pat Hartman]

"Constraint" is a very specific term. It is not possible to enforce constraints between separate databases. You can do whatever you want to in your Access FE to warn about this issue and attempt to prevent it.

 

[Pat Hartman]

Triggers can do a lot but I don't know if they can access other databases.

 

[RogerCooper]

They can according to https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/.

 

[Josef P.]

Triggers can do a lot but I don't know if they can access other databases.

If the access rights are there, it's not a problem.
In the same server instance it is very easy, you only have to declare completely (Database.Schema.TableName).
Otherwise, you can set up a linked server.

One could also consider synchronising the relevant table to be able to use a real constraint.

 

[Pat Hartman]

Sounds like a Trigger will solve the problem. I would guess that you would need two of them though. One in each database so no matter which side changes the field, the change will be verified by the local trigger.

 

[RogerCooper]

If the access rights are there, it's not a problem.
In the same server instance it is very easy, you only have to declare completely (Database.Schema.TableName).
Otherwise, you can set up a linked server.

One could also consider synchronising the relevant table to be able to use a real constraint.

It is the same Server, so I will try this.

 

[Isaac]

I need to create a constraint within SQL Server that enforces the rule NET_WEIGHT <= GROSS_WEIGHT. My problem is that the GROSS_WEIGHT field is in a different SQL server database. How would I create such a constraint? Is there a way I could create a view or some similar object within my database which would have the data from the other database? Or is there a way I can define a constraint to lookup the data directly from the other database?

I wonder if you could incorporate a CLR to check the other database, IF you don't have the link in sql server.

 

[GregDataReno]

At any cost, I'd avoid relying on calling on data in one database to validate data in another - it will just lead to long-term pain, cost, frustration....

Instead, I'd consider a routine to retrieve into a table in your NET_WEIGHT database with the GROSS_WEIGHT values from the other database, and then rely on a constraint in your NET_WIGHT database that checks against the local GROSS_WEIGHT values.

The principle of 'coupling' in software design relates to your solution, specifically, avoiding interdependence between 'modules' (ie between databases in this case).

Goodluck with it.

 

[RogerCooper]

I wonder if you could incorporate a CLR to check the other database, IF you don't have the link in sql server.

What is a CLR?

 

[theDBguy]

What is a CLR?

Common Language Runtime (CLR) Overview - SQL Server

CLR integration with SQL Server allows you to implement some functionalities using any .NET Framework language as SQL Server server-side modules.

learn.microsoft.com

 

[Pat Hartman]

Have you considered changing the design of the two schemas so that the related data is in the SAME database?

 

[RogerCooper]

Have you considered changing the design of the two schemas so that the related data is in the SAME database?

One of the schemas is for our ERP and I can't modify that schema.

 

[Pat Hartman]

Usually ERP's have unassigned columns in tables. Is it possible to find an unassigned column so you can add the data in that table. Of course, there will be no validation so that may not completely solve the problem. You certainly won't be allowed to add any triggers to the ERP so the best you can do is to check the value in the ERP when the value changes in your form. OR add a trigger on your own table to check it.

Depending on how important this relationship is, you might need to check the values EVERY time you use them because if the value in the ERP changes after your validation, you would never catch it unless you validate every time you use the data.

 

[Minty]

How often does the data for that field change in the ERP ?
I'm wondering if you could copy the data into your database on a regular basis if it is fairly static?

 

[RogerCooper]

How often does the data for that field change in the ERP ?
I'm wondering if you could copy the data into your database on a regular basis if it is fairly static?

The GROSS_WEIGHT changes rarely, but when it changes is likely be when the NET_WEIGHT changes.