A
AH
Dear all, I am facing this 'bug' that really drive me nut. I created a trigger for Update script and tested in Enterprise manager to ensure it function correctly. However, when I used both VB or Server explorer to update the table, it gives me error, Couldn't VB just act as a intermidary to make the call and SQL server is supposed to do all the jobs??
To explain it in a simple way, I have 3 TABLES, TABLE1 with column C1(key), C2(char) & C3 (bit). TABLE2 with C1(key) & C2(key) and TABLE3 with C1(key) and C2(key). I wrote a update trigger to fire if C3 changes from 0 to 1. It inserted records into TABLE3 from TABLE2 where TABLE1.C1 = TABLE2.C1. Below are tables with sample data.
TABLE1 C1 C2 C3
A 123 0
B 234 0
C 456 0
TABLE2 C1 C2
A XXX
B XXX
B YYY
B ZZZ
C JJJ
TABLE3 C1 C2
If I change C3 to 1 in the 1st record in TABLE1, it work fine and 1st record in TABLE2 was inserted to TABLE3 in both Enterprise Manager & VS. However, if I change C3 to 1 in the 2nd record, Enterprise Manager will correctly inserted 3 reocrds into TABLE3 but in VS, it flags error 'Key column information is insufficient or incorrect. Too many rows were affected by update'
Could anyone please advise how to stop VS from 'try to be clever' ??
my trigger script as below:
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR UPDATE
AS
IF UPDATE (c1)
declare @C3 as bit
declare @Key as char(10)
set @c3 = (select c3 from inserted)
set @key = (select c1 from INSERTED)
if @c3 = 1
INSERT INTO Table3
(C1, C2)
SELECT C1, c2
FROM Table2
WHERE C1= @Key)
Thank you
To explain it in a simple way, I have 3 TABLES, TABLE1 with column C1(key), C2(char) & C3 (bit). TABLE2 with C1(key) & C2(key) and TABLE3 with C1(key) and C2(key). I wrote a update trigger to fire if C3 changes from 0 to 1. It inserted records into TABLE3 from TABLE2 where TABLE1.C1 = TABLE2.C1. Below are tables with sample data.
TABLE1 C1 C2 C3
A 123 0
B 234 0
C 456 0
TABLE2 C1 C2
A XXX
B XXX
B YYY
B ZZZ
C JJJ
TABLE3 C1 C2
If I change C3 to 1 in the 1st record in TABLE1, it work fine and 1st record in TABLE2 was inserted to TABLE3 in both Enterprise Manager & VS. However, if I change C3 to 1 in the 2nd record, Enterprise Manager will correctly inserted 3 reocrds into TABLE3 but in VS, it flags error 'Key column information is insufficient or incorrect. Too many rows were affected by update'
Could anyone please advise how to stop VS from 'try to be clever' ??
my trigger script as below:
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR UPDATE
AS
IF UPDATE (c1)
declare @C3 as bit
declare @Key as char(10)
set @c3 = (select c3 from inserted)
set @key = (select c1 from INSERTED)
if @c3 = 1
INSERT INTO Table3
(C1, C2)
SELECT C1, c2
FROM Table2
WHERE C1= @Key)
Thank you