asked
help needed to achieve the task
I have a table with fields (Table 1) 1) Agent_Id 2)Name 3)Location (Table2) 1)Agent_Id 2)Status 3)Creation_date Table 2 is a look up table. When ever data is inserted into Table 1 it should check with the Agent id in Table 2. If Agent_Id is available in look up table then it should insert records into Table1.If not reject it. My Environment is sql server 2000 Can some help me to achieve this task.
Three options for this kind of thing: 1. Trigger. - [Create a Trigger][1] that fires on INSERT on Table1 that checks to see if data exists in Table 2 meeting your requirements. If so, then allows the update otherwise reject it. 2. Constraints - Use some sort of [foreign key][2] relationship - this may not be practical if your data is time or status based. This will only allow data to be inserted in Table 1 if a matching record exists in Table 2, assuming you can code it correctly. 3. Stored Procedure. Instead of doing a straight insert statement, write a [stored procedure][3] to encapsulate the logic, and call that instead. To ensure data consistency, wrap all the functionality within the SP in a [transaction][4]. Going on what you've said, I think we can rule out option 2. From the point of brute enforcement, the trigger is the best way to go, as it's difficult to bypass. [1]:
http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx [2]:
http://msdn.microsoft.com/en-us/library/aa933117(SQL.80).aspx [3]:
http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx [4]:
http://msdn.microsoft.com/en-us/library/aa225983(SQL.80).aspx
Why I reckon no to FK: I reckon what the OP is getting at is that the status flag on t2 is what is restricting entry into t1 - in other words, you can't have data put into t1 for agent X if t2 shows agent X as being logged out. If, on the other hand, it just requires a record to be present, then FK all the way!
I would +1 this, but I really can't see why you are saying a foreign key isn't viable here - it seems to me it's the only truly sensible way?
As a general rule, I think the stored procedure option is the best way to go *as long as you can gauruntee all inserts will go through the stored procedure*. If you cannot do that, then the trigger is probably your only realistic option.
I'm going to just say use a foreign key. That is **the** way to enforce relationships between tables. Using a foreign key would mean that inserts into table1 for an agentID that didn't exist in table2 would fail. A few things: 1. AgentID in table1 should be NOT NULL - i.e. it cannot receive NULL values - as a key to another table that is NULL means 'no value' - i.e. not related. 2. AgentID in table2 should have a unique index of some sort on it - it will need a unique index in order for it to be the target of a foreign key. You want something like `CREATE UNIQUE INDEX IX_table2 ON table2 (agentID)` 3. You should then create the foreign key from table1 to table2 as follows: `ALTER TABLE table1 ADD CONSTRAINT FK_table1_table2 FOREIGN KEY (agentID) REFERENCES table2 (agentID)`
@Matt Whitfield +1 This is the way indeed. One thing I would like to add: Add an index to the agentID column of the table1. This is necessary to avoid the [Un-Indexed Foreign Key Gotcha][1]. [1]:
http://www.sqlservercentral.com/articles/T-SQL/68337/
