Relationship between tables of two database

Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-aviAvinash, why don't you create primary key and foreign key constraints to implement referential integrity. That's a better approach than triggers.

gyan.

Originally posted by avishesh
Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-avi :)