Error Numbers in SQL Server

@@ERROR
Returns the error number for the last Transact-SQL statement executed.
When SQL Server completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned.

@@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

USE pubs
GO
UPDATE authors SET au_id = '12345'
WHERE au_id = "54321"
IF @@ERROR = 547
print "A check constraint violation occurred"