Set Error Statement for Users

While Inserting records into your Table, instead of your default INSERT statement it should be like this

BEGIN TRY
       INSERT INTO ur_table ....
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH

Sir, I just try to make in statement, Please guide

CREATE TRIGGER trg_Check_Dublicate_Permit ON  Tankers(Insert,Update) AS
BEGIN TRY
       Select TrailerNo, LoadOn, LB from Tankers Group by TrailerNo, LoadOn, LB  Having Count(1) >1
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH

You can do this using the RAISERROR command in sql. Either you will raise that error in a procedure or trigger, or you will add it in sql server errors and just call that error when it occurs on the system.

To add to the sql server do this:
EXEC sp_addmessage @msgnum=50001,@severity=10,
@msgtext='Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby'
Then when the error occurs, call it like this: RAISERROR ( 50001,10,1 )

You can do the same without adding the error to the sql server errors; put the following in your code where users are supposed to received the error:
RAISERROR ('Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby'
 ,10,1 )

Hope this will help.
Regards

Actually I just created the index to avoid dublication, now I need to display error if any to users.
To do this i am going to create Triger mentioned above.

Please guid how could I would do that?

For Example We need to Display error on the following statement


Declare @Path Varchar(50)
Set @PAth='C:\DataInsertFile.xls'

Begin Try
IF object_id('Tempp') IS NOT NULL DROP TABLE Tempp
EXEC('SELECT * Into Tempp FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source=' + @Path + '; Extended Properties=''''Excel 12.0'''''')...[Sheet1$]')
--EXEC('SELECT * FROM Tempp')
Insert Into Tankers(ComCode,FYear, SU,PermitNo,Receiver,Product,unitvalue,ExchangeRate,LB,LP,via,LF,DP,Trp,invoiceno, SupInvoiceNo,DD250No,orderno,trailerno,
qty,qty15c, loadon,tripno,docno,sealno,drivername,idcard,licenseno,location)
--'Select * from Tempp

select      CC='0'+ convert(varchar(1), a.CC), J.WorkingYEar, b.transno,a.permitno,i.transno,c.transno,a.perunit,a.Er,h.transno,e.transno,f.transno,g.transno,g.transno,d.transno,
            a.invoiceno,a.RefinvNo,a.DD250No,a.OrderNo,a.TLNo,a.DespQty,A.Qtyat15c,a.despdate,a.tripno,a.docno,a.sealno,
            a.driverName,a.IDCardNo,A.LicenseNo,a.Location
from Tempp a
join mtSup b on a.supplier=b.su
Join mtPrd c on a.product=c.product
Join mtTrp d on a.transporter=d.Trp
Join mtLP e on a.lp=loadingpoint
Join mtVia f on a.via=f.via
Join mtDP g on a.dp=g.dischargingpoint
Join mtLB h on a.LC=h.Loadby
Join mtCnCode i on a.Receivers=i.receiver
Join mtYear j on a.despdate >= j.Startingdate and a.despdate <=j.EndingDate

--Join mtContractor j on A.Contractor=j.Contractor
--Exec FuelTableUpdate '\\192.168.0.10\Fuel\DatainsertFile.xls'
--Select * from Tempp
--select * from mtCnCode
End Try

BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH

Using Trigger, you can't catch this error..
You can catch this error only if you a procedure to replace your existing INSRT / UPDATE statements..

Kindly confirm whether this is possilbe in your environment so that if not let's think about an alternate solution..

Missed up several comments while typing this one..
I hope your objective is to inform the user that they are inserting duplicate records or not..

If yes, the easier workaround would be to

* Before Inserting records into your table, check whether those records are already present in your table or not. If yes, then send an error message at that time itself and this would be efficient compared to creating index, catching error message from it and displaying the error message..

SIR, it is possible.
But i haven't idea to implement. Just created triger pasted above. Please guide.

Triger
CREATE TRIGGER trg_Check_Dublicate_Permit ON  Tankers For Insert,Update AS
BEGIN TRY
       Select TrailerNo, LoadOn, LB from Tankers Group by TrailerNo, LoadOn, LB  Having Count(1) >1
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH

Error:
Msg 102, Level 15, State 1, Procedure trg_Check_Dublicate_Permit, Line 6
Incorrect syntax near 'RAISEERROR'.

Ok..Let me give you the steps in detail about how to achieve it:

Problem Scenario:

1. First user INSERT's record into table.
2. Second user Tries to INSERT the same records again.

Workaround:

1. Before inserting check whether the records are present in the table earlier by doing

SELECT count(*) from TANKERS
where TrailerNo = ?
AND LoadOn = ?
and Lb = >

2. If it returns 1, then throw an error message to the user stating that the record already exists with the message which you mentioned.
3. If not, then insert the record successfully into the table.

Using this way, we can ideally prevent the problem at the application level itself..

Almost it is same,
Only change:
User enter data in Bulk as I showed you in my last question and we need to rollback if any one matched with the existing record and raise