I have a problem.

This is in SQL Server 7.0.
Whenever a foreign key violation occurs, we get the standard message like
the following.

UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DepartmentNo'.
The conflict occurred in database 'Assets', table 'DepartmentMaster', column
'DepartmentNumber'.
The statement has been terminated.

How to trap this error in a Stored procedure or in a trigger?.

We can track it using @@Error variable.
But when the key violation occurs SQL Server not even goes into
The update trigger code.

Also, if I have multiple forign key columns and how do I know that
which column is failed?. The error number is 547 for all columns of "Foreign
key violation".

Help will be appriciated.

The same applies to a Primary key violation.