java - 而不是 INSERT 触发器被绕过

Question

我有一个表，其中有一列名为 tripNumber不应该有重复的。

我知道，我可以更改表格并将该列设为 unique但由于某种原因，我无法更改该表，因为它已经在生产中。因此我编写了以下触发器，它基本上执行相同的操作。

    USE [cst_abc]
GO

/****** Object:  Trigger [dbo].[checkTripNumber]    Script Date: 12/21/2019 18:37:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [dbo].[checkTripNumber] on [dbo].[tripDetails]
instead of insert
as 
begin
if exists(select * from [dbo].[tripDetails] where tripNumber = (select [tripNumber] from inserted i))
RAISERROR ('Trip is already there.',15,0);
else
INSERT INTO [cst_abc].[dbo].[tripDetails]
           ([tripNumber]
           ,[noW]
           ,[EndTime]
           ,[someText]
           ,[totalInput]
           ,[totalOutput]
           ,[Difference]
           ,[start]
           ,[end]
           ,[StartTime]
           ,[EndTime]
           ,[serverSync])
SELECT[tripNumber],[noW],[EndTime]
           ,[someText]
           ,[totalInput]
           ,[totalOutput]
           ,[Difference]
           ,[start]
           ,[end]
           ,[StartTime]
           ,[EndTime]
           ,[serverSync] from inserted i
end
GO

它确实按预期工作。我写了一个小的java代码，它基本上启动一个新线程并尝试插入行。我所做的是首先检查行程是否存在，如果存在，则不执行任何操作，否则插入具有特定 id 的新行。

public static void startThread()
    {
        new Thread(() -> {
            try {
                showTimeInMilli("FuncA");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }).start();
        new Thread(() -> {
            try {
                showTimeInMilli("FuncB");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }).start();

    }

public static void showTimeInMilli(String name) throws SQLException
    {
        System.out.println("called from "+name +"Current time is "+System.currentTimeMillis());
        if(checkTripNumber(1))
        {
                System.out.println("called from "+name +" and trip exists.");
        }
        else
        {
            System.out.println("called from "+name +" and inserting new row.");
            SqlUtil.startNewTrip(1,7,"ap1","2019-06-18 07:06:00",5,1576631560);
        }
    }

这里需要注意的是，这个触发器即startTrip可以从多个源触发，并且我发现大多数时候它会同时触发(我存储 epoch 时间，例如从两个源触发，它恰好在 1576934304 处触发)

问题

十分之九的情况符合预期，即它不会添加新行，但有时会添加重复的 tripNumber 。非常感谢任何帮助。

上述 java 代码的理想日志是:

called from FuncACurrent time is 1576933097423
called from FuncBCurrent time is 1576933097423
td before sendig false
called from FuncB and inserting new row.
td before sendig false
called from FuncA and inserting new row.
com.microsoft.sqlserver.jdbc.SQLServerException: Trip is already there.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:367)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at database.SqlUtil.startNewTrip(SqlUtil.java:45)
    at database.Hikari.showTimeInMilli(Hikari.java:122)
    at database.Hikari.lambda$0(Hikari.java:44)
    at java.lang.Thread.run(Unknown Source)
SQL Exception2 com.microsoft.sqlserver.jdbc.SQLServerException: Trip is already there.

意外日志是:

called from FuncACurrent time is 1576933097323
called from FuncBCurrent time is 1576933097323
td before sendig false
called from FuncB and inserting new row.
td before sendig false
called from FuncA and inserting new row.

Answer 1

情况是 INSERT 与检查 tripNumber 存在分开:

INSERT INTO [cst_abc].[dbo].[tripDetails]
           ([tripNumber]
           ,[noW]
           ,[EndTime]
           ,[someText]
           ,[totalInput]
           ,[totalOutput]
           ,[Difference]
           ,[start]
           ,[end]
           ,[StartTime]
           ,[EndTime]
           ,[serverSync])
SELECT[tripNumber],[noW],[EndTime]
           ,[someText]
           ,[totalInput]
           ,[totalOutput]
           ,[Difference]
           ,[start]
           ,[end]
           ,[StartTime]
           ,[EndTime]
           ,[serverSync] 
from inserted i
where NOT EXISTS (SELECT 1 FROM [dbo].[tripDetails] d WHERE i.[tripNumber] = d.[tripNumber]);

无论如何，这种“解决方法”不是一个好的方法，应该引入正常的“UNIQUE”约束。

<小时/>

编辑:

I cant alter the table as it's already in production

如何添加唯一索引(从技术上讲，表没有改变，索引是单独的对象):

CREATE UNIQUE INDEX udx_tripDetails(tripNumber) ON [dbo].[tripDetails](tripNumber)
WITH(ONLINE = ON);