Need help creating trigger to capture single and multiple updates

Transact-SQL
1

I have a process that runs and updates records.This process can update multiple records at the same time. My issue is that our trigger is not catching all the changes.
I am checking if a column is updated and if it has I am reading from the deleted table. I change this to a insert select thinking I should get all the information from that table. However that did not work.

Current trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateAssetHistory]
ON [dbo].[ConfigurationItem]

FOR UPDATE, DELETE
AS
BEGIN

declare @AssetStatus varchar(50)
declare @AssetTag varchar(25)
declare @AssetUser varchar(75)
declare @AssetUserId varchar(42)
declare @EmployeeNumber varchar(15)
declare @CARecid varchar(42)

declare @CreatedBy varchar(50)
declare @CreatedByID varchar(42)
declare @LastModBy varchar(50)
declare @LastModByID varchar(42)
declare @LastModDateTime datetime
declare @CellPagerNo varchar(15)
declare @CountyTag varchar(25)
declare @DisposalType varchar(25)
declare @DistrictSection varchar(50)
declare @eCapsUnit varchar(25)
declare @FacilityCode varchar(15)
declare @FacilityName varchar(50)
declare @Model varchar(80)
declare @ModelNumber varchar(25)
declare @PhoneNUmber varchar(15)
declare @SerialNumber varchar(50)
declare @TicketShipNumber varchar(25)
declare @ParentModifiedBy varchar(50)
declare @TypeofUpdate varchar(25)
declare @BudgetName varchar(50)
declare @CG4UpdatedBy varchar(15)
declare @CG4UserName varchar(50)

-- hh10022017
declare @LastUpdatedBy varchar(50)
declare @LastUpdatedDateTime datetime
declare @LastUpdatedEmpid varchar(15)
declare @LocationFloor varchar(15)
declare @CubicleRoom varchar(50)
declare @LotPallet varchar(50)
declare @EquipmentType varchar(25)
declare @ModelDescription varchar(130)
declare @Ownership varchar(50)
declare @PONumber varchar(25)

declare @Brand varchar(25)
declare @Category varchar(50)
declare @PurchasePrice varchar(10)
declare @IMEI varchar(255)
declare @MEID varchar(15)
declare @ICCID varchar(15)
declare @Manufacturer varchar(80)
declare @PA2197 varchar(15)

select @AssetStatus=d.AssetStatus from deleted d
select @AssetTag=d.AssetTag from deleted d
select @AssetUser=d.AssetUser from deleted d
select @AssetUserId=d.AssetUserID from deleted d
select @EmployeeNumber=d.EmployeeNumber from deleted d
select @CARecid=d.RecID from deleted d
select @CreatedBy=d.LastUpdatedBy from deleted d -- we will test
select @CreatedByID=d.LastUPdatedEmpid from deleted d
select @LastModBy=d.LastUpdatedBy from deleted d
select @LastModByID=d.LastUpdatedEmpid from deleted d
select @LastModDateTime=d.LastModifiedDateTime from deleted d
--????? current user name I am going to get the config asset.lastmodifiedby
--????? CreatedByid ???? current user id config asset.lastmodifiedbyid

select @CellPagerNo=d.CellPagerNo from deleted d
select @CountyTag=d.CountyTag from deleted d
select @DisposalType=d.Disposaltype from deleted d
select @DistrictSection=d.DistrictSection from deleted d
select @eCapsUnit=d.eCapsUnit from deleted d
select @FacilityCode=d.FacilityCode from deleted d
select @FacilityName=d.FacilityName from deleted d
select @Model=d.ModelDescription from deleted d
select @ModelNumber=d.ModelNumber from deleted d
select @PhoneNUmber=d.PhoneNUmber from deleted d
select @SerialNumber=d.SerialNumber from deleted d
select @TicketShipNumber=d.TicketShipNumber from deleted d
--select @ParentModifiedBy=d.LastModifiedby from deleted d
select @BudgetName=d.BudgetName from deleted d
select @CG4UpdatedBy=d.CG4UpdatedBy from deleted d
select @CG4UserName=d.CG4UserName from deleted d
select @TypeofUpdate=d.DataSource from deleted d

-- hh10022017
select @LastUpdatedBy=d.LastUpdatedBy from deleted d
select @LastUpdatedDateTime=d.LastUpdatedDateTime from deleted d
select @LastUpdatedEmpid=d.LastUpdatedEmpid from deleted d
select @LocationFloor=d.LocationFloor from deleted d
select @CubicleRoom=d.CubicleRoom from deleted d
select @LotPallet=d.LotPallet from deleted d
select @EquipmentType=d.EquipmentType from deleted d
select @ModelDescription=d.ModelDescription from deleted d
select @Ownership=d.Ownership from deleted d
select @PONumber=d.PONumber from deleted d

-- hh10022017 2
select @Brand=d.Brand from deleted d
select @Category=d.Category from deleted d
select @PurchasePrice=d.PurchasePrice from deleted d
select @IMEI=d.IMEI from deleted d
select @MEID=d.MEID from deleted d
select @ICCID=d.ICCID from deleted d
select @Manufacturer=d.Manufacturer from deleted d
select @PA2197=d.PA2197 from deleted d

-- UPDATED INFORMATION
if (update(AssetTag)
or update(AssetStatus)
or update(AssetUserID)
or update(LocationFloor)
or update(CubicleRoom)
or update(SerialNumber)
or update(Brand)
or update(Category)
or update(EquipmentType)
or update(ModelDescription)
or update(ModelNumber)
or update(Model)
or update(PONumber)
or update(FacilityCode)
or update(FacilityName)
or update(PurchasePrice)
or update(DistrictSection)
or update(IMEI)
or update(MEID)
or update(ICCID)
or update(Manufacturer)
or update(PA2197)
or update(Room)
or update(LotPallet)
or update(Ownership)
or update(DisposalType)
or update(CountyTag))
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)
VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'UPDATED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197)
END

-- DELETED INFORMATION
-- WHEN DELETED there will not be any information in the inserted table
-- There will be information in the deleted table.

IF NOT EXISTS (SELECT 1 from inserted)
IF EXISTS (SELECT 1 FROM DELETED)
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)
VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'DELETED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197)
End

END

WHAT I WAS THINKING BUT IT DID NOT WORK.....

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

-- DELETED INFORMATION
-- WHEN DELETED there will not be any information in the inserted table
-- There will be information in the deleted table.

IF NOT EXISTS (SELECT 1 from inserted)
IF EXISTS (SELECT 1 FROM DELETED)

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
END

WHAT I AM THINKING ABOUT TRYING#####################

IF @@ROWCOUNT = 1
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)
VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'UPDATED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197)
END
ELSE
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

-- DELETED INFORMATION
-- WHEN DELETED there will not be any information in the inserted table
-- There will be information in the deleted table.
IF @@ROWCOUNT = 1
IF NOT EXISTS (SELECT 1 from inserted)
IF EXISTS (SELECT 1 FROM DELETED)
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)
VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'DELETED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197)
End
ELSE
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

END

I WOULD APPRECIATE ANY INSIGHT... THANKS

2

You said the new code "didn't work". What specifically failed. It looks ok overall, except you'll want to add the update() checks in a where clause to match the original code:

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
WHERE update(AssetTag)
or update(AssetStatus)
or update(AssetUserID)
or update(LocationFloor)
or update(CubicleRoom)
or update(SerialNumber)
or update(Brand)
or update(Category)
or update(EquipmentType)
or update(ModelDescription)
or update(ModelNumber)
or update(Model)
or update(PONumber)
or update(FacilityCode)
or update(FacilityName)
or update(PurchasePrice)
or update(DistrictSection)
or update(IMEI)
or update(MEID)
or update(ICCID)
or update(Manufacturer)
or update(PA2197)
or update(Room)
or update(LotPallet)
or update(Ownership)
or update(DisposalType)
or update(CountyTag)

3

I removed the statements in bold. They were not needed. I also had to corrected one of the fields I was updating. It now works. Thanks for your help.

IF @@ROWCOUNT = 1
IF NOT EXISTS (SELECT 1 from inserted) -->>>> I removed this statement
IF EXISTS (SELECT 1 FROM DELETED) -->>>> I removed this statement
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,comments)
VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'UPDATED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197,@Comments)
END
ELSE
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,comments)
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,DataSource,RecID,BudgetName,CG4UpdatedBy,CG4UserName,'UPDATED',LastUpdatedBy,LastUpdatedDateTime,LastUpdatedEmpid, LocationFloor,CubicleRoom,LotPallet,
EquipmentType,ModelDescription,Ownership,PONumber,Brand,Category,PurchasePrice,IMEI,MEID,ICCID,Manufacturer,PA2197,comments from deleted
--VALUES (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,@TypeofUpdate,@CARecid,@BudgetName,@CG4UpdatedBy,@CG4UserName,'DELETED',@LastUpdatedBy,@LastUpdatedDateTime,@LastUpdatedEmpid,@LocationFloor,@CubicleRoom,@LotPallet,@EquipmentType,@ModelDescription,@Ownership,@PONumber,@Brand,@Category,@PurchasePrice,@IMEI,@MEID,@ICCID,@Manufacturer,@PA2197)
END

4

You're welcome, but @@ROWCOUNT is not an accurate way to check for rows in triggers now. That's why I used a different method.

5

Hi Scott

Please let me know

what method you use to check for rows in triggers
if @@rowcount is not an accurate way

Thanks
:slight_smile:

6

Check the inserted and deleted tables directly.

For example, if you want check for a rowcount of exactly 1 (dangerous, since a trigger could be called to work on 50, 100, or any number of rows changed by a single SQL statement):

IF (SELECT COUNT(*) FROM deleted) = 1

7

oh ok thanks .. :slight_smile:

8

The issue is because of the MERGE statement. MERGE could generate both UPDATEs and DELETEs on the same table. For example, a single MERGE statement could cause 5 UPDATEs and 0 DELETEs. But when the trigger is called, the total rows affected by the MERGE statement will be in @@ROWCOUNT, not just the number of rows that match that particular trigger.

9

oh ok .. got it ,, thank you