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

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2 for Windows NT

    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.

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