Professional Web Applications Themes

Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database; - Microsoft SQL / MS SQL Server

Hi, I am testing a trigger that and I am getting this error message saying "Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database" What I am trying to do through the trigger is not to allow the creation of a new session for which there exists another session with the same cashierid and closingtime is NULL. Thanks Ioannis CREATE TABLE [dbo].[Sessions] ( [id] [int] IDENTITY (1, 1) NOT NULL , [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OpeningTime] [datetime] NOT NULL , [OpeningBalance] [decimal](12, ...

  1. #1

    Default Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database;

    Hi,

    I am testing a trigger that and I am getting this error message saying

    "Another user has modified the contents of this table or view; the database
    row you are modifying no longer exists in the database"

    What I am trying to do through the trigger is not to allow the creation of a
    new session for which there exists another session with the same cashierid
    and closingtime is NULL.

    Thanks
    Ioannis


    CREATE TABLE [dbo].[Sessions] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OpeningTime] [datetime] NOT NULL ,
    [OpeningBalance] [decimal](12, 2) NOT NULL ,
    [ClosingBalance] [decimal](12, 2) NULL ,
    [CalculatedBalance] [decimal](12, 2) NOT NULL ,
    [ClosingTime] [datetime] NULL ,
    [CashierId] [int] NULL
    ) ON [PRIMARY]



    CREATE TRIGGER SessionINSERT ON [dbo].[Sessions]
    FOR INSERT, UPDATE
    AS

    BEGIN
    Declare Count Integer
    Declare ErrorMsg VarChar(100)
    Declare MyCursor CURSOR For Select id,UserName,OpeningBalance,CashierId
    From Inserted

    Declare Id Int
    Declare UserName Char(20)
    Declare OpeningBalance Decimal(12,2)
    Declare cId Int


    Open MyCursor
    Fetch Next From MyCursor
    Into id,UserName,OpeningBalance,cId

    WHILE Fetch_Status <> -1
    BEGIN
    Print id
    Print UserName
    Print OpeningBalance
    Print cId

    Fetch Next From MyCursor
    Into id,UserName,OpeningBalance,cId
    END
    Close MyCursor
    Deallocate MyCursor

    /*
    Select Count = count(*) From Inserted
    Set ErrorMsg = 'Count=' + convert(varchar(100),Count)

    Raiserror (errormsg,16,1)
    */

    If UPDATE(CashierId)
    BEGIN
    Declare CashierId Int
    Declare OpeningTime DateTime


    Select CashierId = s.CashierId, UserName = s.UserName, OpeningTime =
    s.OpeningTime
    From Sessions s, Inserted I
    Where s.CashierId = i.CashierId
    And s.ClosingTime Is NULL
    And i.ClosingTime Is NULL
    And s.Id <> i.Id


    If CashierId is not null
    BEGIN
    ROLLBACK TRANSACTION

    Declare CashierName Char(20)

    Select CashierName=Description
    From Cashiers
    Where id = CashierId


    Set ErrorMsg = 'Cashier ''' + RTrim(CashierName) + ''' is currently
    open by ' + RTrim(UserName) + ' [' + Convert(varchar(20),OpeningTime) +
    ']'

    RAISERROR (ErrorMsg,16, 1)
    END
    END
    END












    Ioannis Demetriades Guest

  2. #2

    Default Re: Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database;

    Hi,

    My understanding of your requirement is as follows:-
    if there is the same cashierID in the table without a ClosingTime, then the
    new INSERT should be rolled back.

    Please check teh following code. I have tested it on a sample table and it
    works fine. Do get back if you need any clarifications on the same.

    CREATE TRIGGER SessionCheck ON dbo.Sessions
    FOR INSERT
    AS
    BEGIN
    DECLARE sessionID int
    DECLARE count int
    SELECT sessionID = cashierID FROM INSERTED
    SELECT count = count(*) FROM Sessions
    WHERE cashierID = sessionID and
    ClosingTime IS NULL
    IF count > 1
    BEGIN
    PRINT 'SESSION ALREADY EXISTS'
    ROLLBACK TRANSACTION
    END
    END

    --
    Regards,
    Mandar Naik


    This posting is provided AS IS with no warranties, and confers no rights.
    "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    news:ekzYF8vPDHA.2852tk2msftngp13.phx.gbl...
    > Hi,
    >
    > I am testing a trigger that and I am getting this error message saying
    >
    > "Another user has modified the contents of this table or view; the
    database
    > row you are modifying no longer exists in the database"
    >
    > What I am trying to do through the trigger is not to allow the creation of
    a
    > new session for which there exists another session with the same cashierid
    > and closingtime is NULL.
    >
    > Thanks
    > Ioannis
    >
    >
    > CREATE TABLE [dbo].[Sessions] (
    > [id] [int] IDENTITY (1, 1) NOT NULL ,
    > [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > [OpeningTime] [datetime] NOT NULL ,
    > [OpeningBalance] [decimal](12, 2) NOT NULL ,
    > [ClosingBalance] [decimal](12, 2) NULL ,
    > [CalculatedBalance] [decimal](12, 2) NOT NULL ,
    > [ClosingTime] [datetime] NULL ,
    > [CashierId] [int] NULL
    > ) ON [PRIMARY]
    >
    >
    >
    > CREATE TRIGGER SessionINSERT ON [dbo].[Sessions]
    > FOR INSERT, UPDATE
    > AS
    >
    > BEGIN
    > Declare Count Integer
    > Declare ErrorMsg VarChar(100)
    > Declare MyCursor CURSOR For Select id,UserName,OpeningBalance,CashierId
    > From Inserted
    >
    > Declare Id Int
    > Declare UserName Char(20)
    > Declare OpeningBalance Decimal(12,2)
    > Declare cId Int
    >
    >
    > Open MyCursor
    > Fetch Next From MyCursor
    > Into id,UserName,OpeningBalance,cId
    >
    > WHILE Fetch_Status <> -1
    > BEGIN
    > Print id
    > Print UserName
    > Print OpeningBalance
    > Print cId
    >
    > Fetch Next From MyCursor
    > Into id,UserName,OpeningBalance,cId
    > END
    > Close MyCursor
    > Deallocate MyCursor
    >
    > /*
    > Select Count = count(*) From Inserted
    > Set ErrorMsg = 'Count=' + convert(varchar(100),Count)
    >
    > Raiserror (errormsg,16,1)
    > */
    >
    > If UPDATE(CashierId)
    > BEGIN
    > Declare CashierId Int
    > Declare OpeningTime DateTime
    >
    >
    > Select CashierId = s.CashierId, UserName = s.UserName, OpeningTime =
    > s.OpeningTime
    > From Sessions s, Inserted I
    > Where s.CashierId = i.CashierId
    > And s.ClosingTime Is NULL
    > And i.ClosingTime Is NULL
    > And s.Id <> i.Id
    >
    >
    > If CashierId is not null
    > BEGIN
    > ROLLBACK TRANSACTION
    >
    > Declare CashierName Char(20)
    >
    > Select CashierName=Description
    > From Cashiers
    > Where id = CashierId
    >
    >
    > Set ErrorMsg = 'Cashier ''' + RTrim(CashierName) + ''' is currently
    > open by ' + RTrim(UserName) + ' [' + Convert(varchar(20),OpeningTime) +
    > ']'
    >
    > RAISERROR (ErrorMsg,16, 1)
    > END
    > END
    > END
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >

    Mandar Naik[MSFT] Guest

  3. #3

    Default Re: Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database;

    Hi Mandar,

    Your trigger works fine. However I still have to take care of any updates on
    "Sessions.CashiersId". Do you know what caused this error message in my
    original trigger?

    Thanks


    "Mandar Naik[MSFT]" <mandarnonline.microsoft.com> wrote in message
    news:%234PFeQwPDHA.4024tk2msftngp13.phx.gbl...
    > Hi,
    >
    > My understanding of your requirement is as follows:-
    > if there is the same cashierID in the table without a ClosingTime, then
    the
    > new INSERT should be rolled back.
    >
    > Please check teh following code. I have tested it on a sample table and it
    > works fine. Do get back if you need any clarifications on the same.
    >
    > CREATE TRIGGER SessionCheck ON dbo.Sessions
    > FOR INSERT
    > AS
    > BEGIN
    > DECLARE sessionID int
    > DECLARE count int
    > SELECT sessionID = cashierID FROM INSERTED
    > SELECT count = count(*) FROM Sessions
    > WHERE cashierID = sessionID and
    > ClosingTime IS NULL
    > IF count > 1
    > BEGIN
    > PRINT 'SESSION ALREADY EXISTS'
    > ROLLBACK TRANSACTION
    > END
    > END
    >
    > --
    > Regards,
    > Mandar Naik
    >
    >
    > This posting is provided AS IS with no warranties, and confers no rights.
    > "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    > news:ekzYF8vPDHA.2852tk2msftngp13.phx.gbl...
    > > Hi,
    > >
    > > I am testing a trigger that and I am getting this error message saying
    > >
    > > "Another user has modified the contents of this table or view; the
    > database
    > > row you are modifying no longer exists in the database"
    > >
    > > What I am trying to do through the trigger is not to allow the creation
    of
    > a
    > > new session for which there exists another session with the same
    cashierid
    > > and closingtime is NULL.
    > >
    > > Thanks
    > > Ioannis
    > >
    > >
    > > CREATE TABLE [dbo].[Sessions] (
    > > [id] [int] IDENTITY (1, 1) NOT NULL ,
    > > [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > > [OpeningTime] [datetime] NOT NULL ,
    > > [OpeningBalance] [decimal](12, 2) NOT NULL ,
    > > [ClosingBalance] [decimal](12, 2) NULL ,
    > > [CalculatedBalance] [decimal](12, 2) NOT NULL ,
    > > [ClosingTime] [datetime] NULL ,
    > > [CashierId] [int] NULL
    > > ) ON [PRIMARY]
    > >
    > >
    > >
    > > CREATE TRIGGER SessionINSERT ON [dbo].[Sessions]
    > > FOR INSERT, UPDATE
    > > AS
    > >
    > > BEGIN
    > > Declare Count Integer
    > > Declare ErrorMsg VarChar(100)
    > > Declare MyCursor CURSOR For Select id,UserName,OpeningBalance,CashierId
    > > From Inserted
    > >
    > > Declare Id Int
    > > Declare UserName Char(20)
    > > Declare OpeningBalance Decimal(12,2)
    > > Declare cId Int
    > >
    > >
    > > Open MyCursor
    > > Fetch Next From MyCursor
    > > Into id,UserName,OpeningBalance,cId
    > >
    > > WHILE Fetch_Status <> -1
    > > BEGIN
    > > Print id
    > > Print UserName
    > > Print OpeningBalance
    > > Print cId
    > >
    > > Fetch Next From MyCursor
    > > Into id,UserName,OpeningBalance,cId
    > > END
    > > Close MyCursor
    > > Deallocate MyCursor
    > >
    > > /*
    > > Select Count = count(*) From Inserted
    > > Set ErrorMsg = 'Count=' + convert(varchar(100),Count)
    > >
    > > Raiserror (errormsg,16,1)
    > > */
    > >
    > > If UPDATE(CashierId)
    > > BEGIN
    > > Declare CashierId Int
    > > Declare OpeningTime DateTime
    > >
    > >
    > > Select CashierId = s.CashierId, UserName = s.UserName, OpeningTime
    =
    > > s.OpeningTime
    > > From Sessions s, Inserted I
    > > Where s.CashierId = i.CashierId
    > > And s.ClosingTime Is NULL
    > > And i.ClosingTime Is NULL
    > > And s.Id <> i.Id
    > >
    > >
    > > If CashierId is not null
    > > BEGIN
    > > ROLLBACK TRANSACTION
    > >
    > > Declare CashierName Char(20)
    > >
    > > Select CashierName=Description
    > > From Cashiers
    > > Where id = CashierId
    > >
    > >
    > > Set ErrorMsg = 'Cashier ''' + RTrim(CashierName) + ''' is
    currently
    > > open by ' + RTrim(UserName) + ' [' + Convert(varchar(20),OpeningTime)
    +
    > > ']'
    > >
    > > RAISERROR (ErrorMsg,16, 1)
    > > END
    > > END
    > > END
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    >
    >

    Ioannis Demetriades Guest

  4. #4

    Default Re: Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database;

    Hi,

    Regarding the update check, you will need a seperate trigger FOR UPDATE
    where you can use the similar logic based on the updated column.

    I am adding the code here. It works in my scenario. Please revert if you
    have any queries.

    CREATE TRIGGER SessionCheckUpd ON dbo.Sessions
    FOR UPDATE
    AS
    BEGIN
    DECLARE sessionID int
    DECLARE count int
    If UPDATE(SessionID)
    BEGIN
    SELECT sessionID = CashiersId FROM INSERTED
    SELECT count = count(*) FROM Sessions
    WHERE CashiersId= sessionID and ClosingTime iS NULL
    IF count > 1
    BEGIN
    PRINT 'SESSION ALREADY EXISTS'
    ROLLBACK TRANSACTION
    END
    END
    END
    --
    Regards,
    Mandar Naik


    This posting is provided AS IS with no warranties, and confers no rights.
    "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    news:eV##aZwPDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi Mandar,
    >
    > Your trigger works fine. However I still have to take care of any updates
    on
    > "Sessions.CashiersId". Do you know what caused this error message in my
    > original trigger?
    >
    > Thanks
    >
    >
    > "Mandar Naik[MSFT]" <mandarnonline.microsoft.com> wrote in message
    > news:%234PFeQwPDHA.4024tk2msftngp13.phx.gbl...
    > > Hi,
    > >
    > > My understanding of your requirement is as follows:-
    > > if there is the same cashierID in the table without a ClosingTime, then
    > the
    > > new INSERT should be rolled back.
    > >
    > > Please check teh following code. I have tested it on a sample table and
    it
    > > works fine. Do get back if you need any clarifications on the same.
    > >
    > > CREATE TRIGGER SessionCheck ON dbo.Sessions
    > > FOR INSERT
    > > AS
    > > BEGIN
    > > DECLARE sessionID int
    > > DECLARE count int
    > > SELECT sessionID = cashierID FROM INSERTED
    > > SELECT count = count(*) FROM Sessions
    > > WHERE cashierID = sessionID and
    > > ClosingTime IS NULL
    > > IF count > 1
    > > BEGIN
    > > PRINT 'SESSION ALREADY EXISTS'
    > > ROLLBACK TRANSACTION
    > > END
    > > END
    > >
    > > --
    > > Regards,
    > > Mandar Naik
    > >
    > >
    > > This posting is provided AS IS with no warranties, and confers no
    rights.
    > > "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    > > news:ekzYF8vPDHA.2852tk2msftngp13.phx.gbl...
    > > > Hi,
    > > >
    > > > I am testing a trigger that and I am getting this error message saying
    > > >
    > > > "Another user has modified the contents of this table or view; the
    > > database
    > > > row you are modifying no longer exists in the database"
    > > >
    > > > What I am trying to do through the trigger is not to allow the
    creation
    > of
    > > a
    > > > new session for which there exists another session with the same
    > cashierid
    > > > and closingtime is NULL.
    > > >
    > > > Thanks
    > > > Ioannis
    > > >
    > > >
    > > > CREATE TABLE [dbo].[Sessions] (
    > > > [id] [int] IDENTITY (1, 1) NOT NULL ,
    > > > [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    > > > [OpeningTime] [datetime] NOT NULL ,
    > > > [OpeningBalance] [decimal](12, 2) NOT NULL ,
    > > > [ClosingBalance] [decimal](12, 2) NULL ,
    > > > [CalculatedBalance] [decimal](12, 2) NOT NULL ,
    > > > [ClosingTime] [datetime] NULL ,
    > > > [CashierId] [int] NULL
    > > > ) ON [PRIMARY]
    > > >
    > > >
    > > >
    > > > CREATE TRIGGER SessionINSERT ON [dbo].[Sessions]
    > > > FOR INSERT, UPDATE
    > > > AS
    > > >
    > > > BEGIN
    > > > Declare Count Integer
    > > > Declare ErrorMsg VarChar(100)
    > > > Declare MyCursor CURSOR For Select
    id,UserName,OpeningBalance,CashierId
    > > > From Inserted
    > > >
    > > > Declare Id Int
    > > > Declare UserName Char(20)
    > > > Declare OpeningBalance Decimal(12,2)
    > > > Declare cId Int
    > > >
    > > >
    > > > Open MyCursor
    > > > Fetch Next From MyCursor
    > > > Into id,UserName,OpeningBalance,cId
    > > >
    > > > WHILE Fetch_Status <> -1
    > > > BEGIN
    > > > Print id
    > > > Print UserName
    > > > Print OpeningBalance
    > > > Print cId
    > > >
    > > > Fetch Next From MyCursor
    > > > Into id,UserName,OpeningBalance,cId
    > > > END
    > > > Close MyCursor
    > > > Deallocate MyCursor
    > > >
    > > > /*
    > > > Select Count = count(*) From Inserted
    > > > Set ErrorMsg = 'Count=' + convert(varchar(100),Count)
    > > >
    > > > Raiserror (errormsg,16,1)
    > > > */
    > > >
    > > > If UPDATE(CashierId)
    > > > BEGIN
    > > > Declare CashierId Int
    > > > Declare OpeningTime DateTime
    > > >
    > > >
    > > > Select CashierId = s.CashierId, UserName = s.UserName,
    OpeningTime
    > =
    > > > s.OpeningTime
    > > > From Sessions s, Inserted I
    > > > Where s.CashierId = i.CashierId
    > > > And s.ClosingTime Is NULL
    > > > And i.ClosingTime Is NULL
    > > > And s.Id <> i.Id
    > > >
    > > >
    > > > If CashierId is not null
    > > > BEGIN
    > > > ROLLBACK TRANSACTION
    > > >
    > > > Declare CashierName Char(20)
    > > >
    > > > Select CashierName=Description
    > > > From Cashiers
    > > > Where id = CashierId
    > > >
    > > >
    > > > Set ErrorMsg = 'Cashier ''' + RTrim(CashierName) + ''' is
    > currently
    > > > open by ' + RTrim(UserName) + ' [' +
    Convert(varchar(20),OpeningTime)
    > +
    > > > ']'
    > > >
    > > > RAISERROR (ErrorMsg,16, 1)
    > > > END
    > > > END
    > > > END
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Mandar Naik[MSFT] Guest

  5. #5

    Default Re: Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database;

    Hi Mandar,

    Thanks for your advice. I took your code and made some minor modifications
    and I noticed that when i run INSERT and UPDATE sql statements the triggers
    behave as expected. It's only when I try to modify the data through the
    Enterprise manager that I get this message. My question is whether i am
    doing something fundamentally wrong with the triggers or is it something
    that has to do with the cursor that is used to fetch the data in the
    enterprise manager? Where can i get some info about this error message?

    Thanks

    --- CODE -------------

    CREATE TABLE [dbo].[Sessions] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [UserName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OpeningTime] [datetime] NOT NULL ,
    [OpeningBalance] [decimal](12, 2) NOT NULL ,
    [ClosingBalance] [decimal](12, 2) NULL ,
    [CalculatedBalance] [decimal](12, 2) NOT NULL ,
    [ClosingTime] [datetime] NULL ,
    [CashierId] [int] NULL
    ) ON [PRIMARY]


    drop trigger sessionINS
    go
    CREATE TRIGGER SessionINS ON dbo.Sessions
    FOR INSERT
    AS
    BEGIN
    /*
    Print 'INSERT Trigger'
    */

    DECLARE iCashierId int
    DECLARE count int
    Declare iClosingTime DateTime

    SELECT iCashierId = cashierID, iClosingTime = ClosingTime
    FROM INSERTED

    If iClosingTime Is not NULL
    Return

    SELECT count = count(*) FROM Sessions
    WHERE cashierID = iCashierId and
    ClosingTime IS NULL

    IF count > 1
    BEGIN
    Raiserror ('SESSION ALREADY EXISTS (INSERT)',16,1)
    ROLLBACK TRANSACTION
    END
    END


    CREATE TRIGGER SessionUPD ON dbo.Sessions
    FOR UPDATE
    AS
    BEGIN
    DECLARE iCashierId int
    DECLARE Count int
    Declare iClosingTime DateTime

    /*
    Print 'UPDATE Trigger'
    */
    If UPDATE(CashierId)
    BEGIN
    /*
    Print 'UPDATE Trigger: Updating CashierId'
    */
    SELECT iClosingTime = ClosingTime, iCashierId = CashierId
    FROM INSERTED

    If iClosingTime Is NOT NULL
    Return

    SELECT count = count(*)
    FROM Sessions
    WHERE CashierId= iCashierId
    And ClosingTime Is NULL

    IF count > 1
    BEGIN
    Raiserror ('SESSION ALREADY EXISTS (UPDATE)',16,1)
    ROLLBACK TRANSACTION
    END
    END
    END


    Ioannis Demetriades Guest

Similar Threads

  1. User Database Table
    By Bill Buppert in forum ASP.NET Security
    Replies: 0
    Last Post: October 28th, 05:00 PM
  2. Replies: 0
    Last Post: August 9th, 02:36 PM
  3. #24932 [NEW]: pg_lo_read_all returns a modified large object from database
    By leitgebj at barada dot canisius dot edu in forum PHP Development
    Replies: 0
    Last Post: August 4th, 12:27 PM
  4. Modifying a Database query
    By jwrnana in forum Microsoft Access
    Replies: 1
    Last Post: August 1st, 07:15 PM
  5. Replies: 2
    Last Post: July 14th, 05:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139