CODEWITHSUNDEEP
javasql-serverdatabase-trigger
driftking9987
driftking9987

Reputation: 1713

Instead of INSERT trigger getting bypassed

I have a table which has a column named tripNumber which shouldnt have a duplicate.

I know, that I can alter the table and make that column unique but for some reason, I cant alter the table as it's already in production. Hence I wrote the following trigger which basically does the same thing.

    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

It does work as expected. I wrote a small java code which basically starts a new thread and try to insert rows. What I do is to first check if a trip exists, if yes, does nothing, else inserts a new row with a particular 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);
        }
    }

Point to be noted here is that, this trigger i.e to startTrip can be triggered from multiple source and I have seen that most of the times it gets triggered at the same time ( I store the epoch time for example from two sources it gets triggered at exactly 1576934304)

Problem

9 out of 10 times it works expected, i.e it doesnt let a new row added but at times, it adds a duplicate tripNumber. Any help is highly appreciated.

Ideal Log for the above java code is :

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.

Un-expected Log is :

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.

Upvotes: 0

Views: 40

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

The case is INSERT is separated from checking for tripNumber existence:

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]);

Anyway this kind of "workaround" is not good approach and normal "UNIQUE" constraint should be introduced instead.

EDIT:

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

How about adding unique index(technically speaking table is not altered, index is separate object):

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

Upvotes: 1

Related Questions