BUG: Error 513 on Update/Insert on Join When No Rows Affected

ID: Q118413

The information in this article applies to:

BUG#  NT:  844 (4.2)
    OS/2: 1868 (4.2)

SYMPTOMS

If a table has a column of a user-defined data type with a rule associated with that data type, and if this table only has a single row, attempting to UPDATE or INSERT INTO this table based on a table join with another table may cause:

Message 513, Level 16, State 1
A column insert or update conflicts with a rule imposed by a previous
CREATE RULE command. The command was aborted.

The above error will occur even if that single row does not satisfy the join condition.


WORKAROUND

Instead of binding the rule to the data type, bind it to the table column only.


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Windows NT. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


MORE INFORMATION

For example, the following script generates error 513 even if no row should be affected: 


   sp_addtype number_t, int
   go
   create rule number_t_rule as @num >= 0
   go
   sp_bindrule number_t_rule, number_t

   create table x (a number_t, b number_t)
   go
   create table y (c number_t)
   go

   insert x values(12,0)
   insert y values(13)
   go

   update x set b = b-1
   from x, y
   where x.a = y.c
   go

   insert x
   select a, b-1
   from x, y
   where x.a = y.c
   go

NOTE: If a join is not involved or if the table x has zero or multiple rows, the problem will not happen. 


Keywords          : kbprg SSrvProg kbbug4.20 SSrvWinNT 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 20, 1999