Update trigger + autonumeric Key column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update trigger + autonumeric Key column

Author

Topic

Fatalsniper
Starting Member

45 Posts

Posted - 2005-08-07 : 18:21:13

Hi there,

i ran into this problem, I'm developing this app in VB, using SQL Server 7.0. I have two triggers(Update, Insert) in the main table which PK is integer autonumeric. They just mantain some integrity, with another related table, (when I insert in the main table I either insert a new record in another secondary table, or update any changes in the updated fields of the main table that exist as well in that other secondary table.
When i try to add a new record to the database, says, I get this error: "[Microsoft Cursor Engine] Key column information is insufficient or incorrect. Too many rows were affected by update."
The data is inserted well into the table, but from VB I'm not able to resync the value of the PK, which is autonumeric right after recordset.update. all I get is 0, but when I check the table the record was perfectly inserted.
So i dropped the triggers, and it works right, then I'm able to get the PK value and resync the value of it with the controls itout raisin' any error. But i really need those triggers, They work like they're supposed to...but i can't work With ADO and VB.I don't how to fix this

Any idea?

Thanks in advance...

DROP TRIGGER I_AspirantRelative
GO

CREATE TRIGGER I_AspirantRelative
ON Aspirant
FOR INSERT
AS
DECLARE
@nReg_FK int,
@lastname as nvarchar(30),
@name as nvarchar(30),
@Sex as char(1),
@Nation as int,
@tDoc as int,
@nDoc as int,
@date as datetime

SELECT @nReg_FK = nReg_Asp FROM Inserted
SELECT @Lastname = lastname_Asp FROM Inserted
SELECT @name = name_Asp FROM Inserted
SELECT @Sex = Sex_Asp FROM Inserted
SELECT @country = ID_Nat_Asp_FK FROM Inserted
SELECT @tDoc = tdoc_Asp FROM Inserted
SELECT @nDoc = nDoc_Asp FROM Inserted
SELECT @date = date_Asp FROM Inserted

BEGIN
Insert into Relatives VALUES(@nReg_FK,1,@lastname,@name,@tDoc,@nDoc,@date,@Sex,@Nation,null,0,null,null,null,0,null,null,null,null,null,null,null,null,0,null,null,null,null,null)
END

GO

DROP TRIGGER U_AspirantRelative
GO

CREATE TRIGGER U_AspirantRelative
ON Aspirant
FOR UPDATE
AS
DECLARE
@nReg_FK int,
@lastname as nvarchar(30),
@name as nvarchar(30),
@Sex as char(1),
@Nation as int,
@tDoc as int,
@nDoc as int,
@date as datetime

SELECT @nReg_FK = nReg_Asp FROM Inserted
SELECT @Lastname = lastname_Asp FROM Inserted
SELECT @name = name_Asp FROM Inserted
SELECT @Sex = Sex_Asp FROM Inserted
SELECT @country = ID_Nat_Asp_FK FROM Inserted
SELECT @tDoc = tdoc_Asp FROM Inserted
SELECT @nDoc = nDoc_Asp FROM Inserted
SELECT @date = date_Asp FROM Inserted

BEGIN
UPDATE Relatives SET Lastname_Rel=@Lastname, Name_Fam=@Name, tDoc_Fam=@tDoc, nDoc_Fam=@nDoc, date_Fam=@date, Sex_Fam=@Sex, ID_Nat_Asp_FK=@country, Parent_Rel_FK=null WHERE nReg_Asp_FK=@nReg_FK and ID_Rel=1
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-08 : 15:56:25

I think the issue is that your triggers need to be able to deal with muli-row inserts and updates. Don't use variables just use inserted table. Something like this:


CREATE TRIGGER I_AspirantRelative
ON Aspirant
FOR INSERT
AS

Insert into Relatives
	(<colList>)
select	nReg_Asp
	,1
	,lastname_Asp
	,name_Asp
	,tdoc_Asp
	,nDoc_Asp
	,date_Asp
	,Sex_Asp
	,ID_Nat_Asp_FK
	,null
	,0
	,null
	,null
	,null
	,0
	,null
	,null
	,null
	,null
	,null
	,null,
	null,
	null,
	0,
	null,
	null,
	null,
	null,
	null
from	inserted
go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources