SQLTeam.com | Weblogs | Forums

Tsql trigger only inserts one record


#1

My tsql trigger was only inserting one record when multiple updates had occurred. I changed my insert statement. I think my problem has to do with getting the next sequence number.

I am not sure if I can do select NEXT VALUE FOR AssetHistory_Seq,

Prior I got the values doing the following "VALUES (NEXT VALUE FOR AssetHistory_Seq,"

New statement. Is this valid?
BEGIN
INSERT INTO AssetHistory
(RecId,CreatedDateTime,CreatedBy,CreatedByID,LastModDateTime,LastModBy,LastModByID,AssetStatus,AssetTag,AssetUser,AssetUserID,EmployeeNumber,CellPagerNo,CountyTag,DisposalType,DistrictSection,eCapsUnit,FacilityCode,
FacilityName,Model,ModelNumber,PhoneNUmber,SerialNumber,TicketShipNumber,typeofupdate,configassetsid,BudgetName,CG4UpdatedBy,CG4UserName,TransactionType,LastUpdatedBy,LastUpdatedDateTime,LastUpdatedEmpid,
LocationFloor,CubicleRoom,LotPallet,EquipmentType,ModelDescription,Ownership,PONumber,Brand,Category,PurchasePrice,IMEI,MEID,ICCID,Manufacturer,PA2197)
select NEXT VALUE FOR AssetHistory_Seq,GETDATE(),CreatedBy,CreatedByID,GETDATE(),LastModBy,LastModByID,AssetStatus,AssetTag,AssetUser,AssetUserID,EmployeeNumber,CellPagerNo,CountyTag,DisposalType,DistrictSection,eCapsUnit,FacilityCode,FacilityName,
Model,ModelNumber,PhoneNUmber,SerialNumber,TicketShipNumber,'','',BudgetName,CG4UpdatedBy,CG4UserName,'UPDATED',LastUpdatedBy,LastUpdatedDateTime,LastUpdatedEmpid, LocationFloor,CubicleRoom,LotPallet,
EquipmentType,ModelDescription,Ownership,PONumber,Brand,Category,PurchasePrice,IMEI,MEID,ICCID,Manufacturer,PA2197 from inserted
END

thanks


#2

Looks ok to me. The problem must be somewhere else(?).


#3

Let me ask this. If an record is updated then there will be information in the inserted temp table. If an record is deleted then there will be information in the deleted temp table?


#4

Yes.

If a row is updated, there will be a "before" row in the inserted "table", and the "after" row in the deleted table.

If a row is deleted, the row will be in the deleted table; the inserted table will be empty for a DELETE.