adamtrask
asked on
Trigger prevents deletion from SQL Server Table
Hi,
I use the code below to delete records from a table called Distribution. The deletion works fine each time. But I need to have an after delete trigger for that table and each time I create the trigger the delete code gives the error:
String or binary data would be truncated.
The statement has been terminated.
Here is the code for the Trigger:
ALTER Trigger [dbo].[DelTicket] on [dbo].[Distribution]
After Delete As
Begin
if ((Select count(*) from Deleted) > 1)
Rollback Tran
else
Insert Into dbo.ClosedTickets
Select * From
Deleted
End
And here is the code for the delete statement:
public void deleteTicket(int selectedID)
{
SqlConnection con = null;
con = new SqlConnection("Server=PC;; Database=S upportProj ect;integr ated security=True");
SqlCommand comm = new SqlCommand("delete from Distribution where ID=@ID",con);
comm.Parameters.AddWithVal ue("@ID", System.Data.SqlDbType.Int) ;
comm.Parameters["@ID"].Val ue = selectedID;
con.Open();
// Execute the command
comm.ExecuteNonQuery();
con.Close();
}
I use the code below to delete records from a table called Distribution. The deletion works fine each time. But I need to have an after delete trigger for that table and each time I create the trigger the delete code gives the error:
String or binary data would be truncated.
The statement has been terminated.
Here is the code for the Trigger:
ALTER Trigger [dbo].[DelTicket] on [dbo].[Distribution]
After Delete As
Begin
if ((Select count(*) from Deleted) > 1)
Rollback Tran
else
Insert Into dbo.ClosedTickets
Select * From
Deleted
End
And here is the code for the delete statement:
public void deleteTicket(int selectedID)
{
SqlConnection con = null;
con = new SqlConnection("Server=PC;;
SqlCommand comm = new SqlCommand("delete from Distribution where ID=@ID",con);
comm.Parameters.AddWithVal
comm.Parameters["@ID"].Val
con.Open();
// Execute the command
comm.ExecuteNonQuery();
con.Close();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A whole column was missing and I spent most of the day trying to look for errors in the code...
I guess experience makes perfect, and I don't have much of that. So I learn the hard way.
Thank you so much for your help.