Hi All,I getting error:IDENTITY_INSERT is already ON for table 'PRAF.dbo.tblBrand'. Cannot perform SET operation for table 'eAsset.dbo.tblBrand'.Alter TRIGGER BrandTriggerINSERT ON tblBrand after INSERT ASDECLARE @ID int, @Description nvarchar(max)SET @ID = (SELECT BrandID FROM inserted)SET @Description = (SELECT [Description] FROM inserted)SET IDENTITY_INSERT PRAF.dbo.tblBrand ONinsert into Praf.dbo.tblBrand(BrandID,[Description],TypeID)select BrandID,Description,TypeID from eAsset.dbo.tblBrand where BrandID=@IDSET IDENTITY_INSERT PRAF.dbo.tblBrand OFFGO
Then, i change to below code by unqoute the set identity_insert, i got error:Violation of PRIMARY KEY constraint 'PK_tblBrand'. Cannot insert duplicate key in object 'dbo.tblBrand'.The statement has been terminated.Alter TRIGGER BrandTriggerINSERT ON tblBrand after INSERT ASDECLARE @ID int, @Description nvarchar(max)SET @ID = (SELECT BrandID FROM inserted)SET @Description = (SELECT [Description] FROM inserted)--SET IDENTITY_INSERT PRAF.dbo.tblBrand ONinsert into Praf.dbo.tblBrand(BrandID,[Description],TypeID)select BrandID,Description,TypeID from eAsset.dbo.tblBrand where BrandID=@ID--SET IDENTITY_INSERT PRAF.dbo.tblBrand OFFGO
How can i handle this issue.What actually i needed are:Database: eAsset Tablename: tblBrandtrigger after insert success and duplicate the new insert record with exactly same BrandID to another database: PRAF, tablename: tblBrand.Please advise.Thank you.Regards,Micheale