72690

How do you check constraints from another table when entering a row into a table?

Question:

Say I have two tables Account and Withdraw. Account has an attribute Balance. Every time a value is entered into the table Withdraw I would like to check if there is sufficient balance to do so. If yes, I'd like to subtract that amount from the Balance.

This isn't exactly what I want to do, but a simplified version of my requirement.

PS: Is "check constraints" the correct phrase? I'm not really sure. Thanks!

Answer1:

You can create a function where you verify if the balance is > than your @value.<br /> And then you add that function to the constraint.

Try to look at this:

<a href="https://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table" rel="nofollow">Can a Check constraint relate to another table?</a>

Answer2:

What you're wanting not a constraint. You're looking for a trigger. Take a look at this example.

IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[Account] GO CREATE TABLE dbo.Account ( AccountID INT NOT NULL , AccountBalance DECIMAL(19, 2) NOT NULL ) GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transaction]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[Transaction] GO CREATE TABLE dbo.[Transaction] ( TransactionID INT NOT NULL IDENTITY(1, 1) , AccountID INT NOT NULL , TransactionAmount DECIMAL(19, 2) NOT NULL ) GO CREATE TRIGGER dbo.TRI_Transaction ON dbo.[Transaction] AFTER INSERT AS UPDATE a SET a.AccountBalance = a.AccountBalance + i.TransactionAmount FROM Account a JOIN INSERTED i ON ( a.AccountID = i.AccountID ) GO INSERT INTO dbo.Account ( AccountID, AccountBalance ) VALUES ( 1234, 0 ) SELECT * FROM dbo.Account a LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID ) INSERT INTO dbo.[Transaction] ( AccountID, TransactionAmount ) VALUES ( 1234, 10 ) SELECT * FROM dbo.Account a LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID ) INSERT INTO dbo.[Transaction] ( AccountID, TransactionAmount ) VALUES ( 1234, 20 ) SELECT * FROM dbo.Account a LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID ) INSERT INTO dbo.[Transaction] ( AccountID, TransactionAmount ) VALUES ( 1234, -15 ) SELECT * FROM dbo.Account a LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

Answer3:

This is short example:

INSERT INTO mytable (id, name) SELECT 1, 'test' WHERE NOT EXISTS(SELECT id FROM myanother_table WHERE id = 1) <blockquote>

<strong>Note:</strong> Its hard to assume anything without understand or taking a look of OP's code. So i have just provided example.

</blockquote>

Recommend

  • Limits for executing stored procedure with parameter through Excel ADO connection
  • SQL Server 2008 - Concatenate String
  • SQL Reports for Manager/Supervisor/Employee Hierarchy from an Employee Table
  • Definition of slack variable in time window routing
  • Could not find or load main class org.apache.hadoop.fs.FsShell
  • Django ManyToMany relationship with abstract base - not possible, but is there a better way?
  • Is it possible to have space between cells in iTextPdf?
  • How to use i18t for texts in the source code of Angular project?
  • Column Nullability/Optionality: NULL vs NOT NULL
  • Joining Model with Table Laravel 5.2
  • Wordpress - Delete all tags by Post ID
  • String encoding problem on PdoStatement->bindParam()?
  • if I want to find what's referencing an object in SQL Server, is searching syscomments comprehe
  • Installing Python modules with Anaconda or Canopy
  • Is there any way to use wpdb prepare statements for array implode(' OR ', $myArray)?
  • Regex for nested values
  • How to Model Ternary Relationship in CakePhp?
  • Aptana 3 remove bundle (jquery)
  • Email verification using google app script and google forms
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Recording logins for password protected directories
  • Splitting given String into two variables - php
  • Check if a string to interpolate provides expected placeholders
  • RestKit - RKRequestDelegate does not exist
  • Comma separated Values
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • Angular 2 constructor injection vs direct access
  • how does django model after text[] in postgresql [duplicate]
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?