"Joseph I. Ceasar" <joseph@harvesttechnology.com> wrote in message
news:eER%234UQiHHA.4680@TK2MSFTNGP06.phx.gbl...
>I have a very strange table on which I cannot create a delete trigger. I
>am using SQL Server 2005. The INSERT trigger does work on this table. The
>UPDATE does not work either. I have created the same table on two
>databases on two separate servers!
>
> Here is the table:
>
> CREATE TABLE [dbo].[Borrowings3](
> [BookId] [smallint] NOT NULL,
> [BorrowDate] [datetime] NULL,
> [BorrowAmt] [numeric](20, 8) NULL,
> [Rate] [numeric](20, 8) NULL,
> [UpdateDate] [datetime] NULL,
> [UpdateUser] [varchar](128) NULL
> ) ON [PRIMARY]
> GO
>
>
> And here it the trigger:
>
>
> CREATE TRIGGER [dbo].[tr_Borrowings3] ON [dbo].[Borrowings3] FOR DELETE AS
> BEGIN
> select * into mydeleted from deleted
> END
>
>
> When I try to delete a record, this is the message that I get:
>
> ---------------------------
> Microsoft SQL Server Management Studio
> ---------------------------
> No rows were deleted.
>
> A problem occurred attempting to delete row 1.
> Error Source: Microsoft.VisualStudio.DataTools.
> Error Message: The row value(s) updated or deleted either do not make the
> row unique or they alter multiple rows(2 rows).
>
> Correct the errors and attempt to delete the row again or press ESC to
> cancel the change(s).
> ---------------------------
> OK Help
> ---------------------------
>
>
> If I remove the trigger, then everything works just fine.
>
>
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uNRKONRiHHA.4228@TK2MSFTNGP03.phx.gbl...
> Add a Primary Key constraint to the table so it knows which rows to
> delete.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph I. Ceasar" <joseph@harvesttechnology.com> wrote in message
> news:eER%234UQiHHA.4680@TK2MSFTNGP06.phx.gbl...
>>I have a very strange table on which I cannot create a delete trigger. I
>>am using SQL Server 2005. The INSERT trigger does work on this table.
>>The UPDATE does not work either. I have created the same table on two
>>databases on two separate servers!
>>
>> Here is the table:
>>
>> CREATE TABLE [dbo].[Borrowings3](
>> [BookId] [smallint] NOT NULL,
>> [BorrowDate] [datetime] NULL,
>> [BorrowAmt] [numeric](20, 8) NULL,
>> [Rate] [numeric](20, 8) NULL,
>> [UpdateDate] [datetime] NULL,
>> [UpdateUser] [varchar](128) NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> And here it the trigger:
>>
>>
>> CREATE TRIGGER [dbo].[tr_Borrowings3] ON [dbo].[Borrowings3] FOR DELETE
>> AS
>> BEGIN
>> select * into mydeleted from deleted
>> END
>>
>>
>> When I try to delete a record, this is the message that I get:
>>
>> ---------------------------
>> Microsoft SQL Server Management Studio
>> ---------------------------
>> No rows were deleted.
>>
>> A problem occurred attempting to delete row 1.
>> Error Source: Microsoft.VisualStudio.DataTools.
>> Error Message: The row value(s) updated or deleted either do not make the
>> row unique or they alter multiple rows(2 rows).
>>
>> Correct the errors and attempt to delete the row again or press ESC to
>> cancel the change(s).
>> ---------------------------
>> OK Help
>> ---------------------------
>>
>>
>> If I remove the trigger, then everything works just fine.
>>
>>
>>
>
>

> And here it the trigger:
>
>
> CREATE TRIGGER [dbo].[tr_Borrowings3] ON [dbo].[Borrowings3] FOR DELETE AS
> BEGIN
> select * into mydeleted from deleted
> END

> 3) A newbie will often fix this with a base table and an INSERT INTO
> change in the trigger. Wrong! You might want to talk the accounting
> department about the proper way to do audit trails. The data is kept
> outside of the schema so extra meta-data columns like "UpdateDate" and
> "UpdateUser" should not be here -- unless you really want to go to
> jail :)