Hi all,

Please could you check the below case and advise what would be the best way to proceed.

I have a table with more than 10K records in it. I want to restrict any data insert into the table if the value of column 1 is 'xyz'. The table currently has multiple rows with column 1 as 'xyz' and these rows should remain in the table.

May be this can be achieved by triggers, but I am not sure if it would work as there are already rows with column 1 as 'xyz'.

Thank you for your time.

Arun

If I understand your requirements correctly, this would be better handled with a constraint than a trigger. But first, clean up your existing data.

I confess I don't understand the purpose of a column that is restricted to a constant value....

In other words you want the existing rows that contain your 'xyz' constant value to be left "as is" and you want the user to be able to UPDATE or DELETE those rows at will, but you don't want any more rows to be added with 'xyz' in that column.

If I understand the problem correctly, I don't think that a constraint can do what you want. Depending on which database engine you are using, a trigger may be able to provide that functionality.

-PatP

thanks for the comments.

Sorry...may be I did not explain the case clearly.

I am having a table with CustomerID and their PhoneNumbers. I want to limit any new phone numbers being added to the customer.

So customer xyz might be having 5 phone numbers right now. I now need to restrict any new phone numbers being added to this customer. I need to keep the details of the 5 phone numbers already assigned to this customer, so I cannot delete those entries.

I need to restrict this data insert for specific customers, so a blanket ban on insert will not work.

Thank you

Arun

Which DBMS are you using?

Microsoft SQL Server 2008.

Thanks

So some of the customers will be limited to five phone numbers, some will be limited to more or less than five, and others will have no limit???

Please post the DDL to build the table or tables involved, and the sample data for at least five customers. There are too many potential twists for me to even guess at a solution yet, and the schema snd sample data should give all of us a better handle on what you need.

-PatP

that was my feeling too, right from post #1