OK,

ihave changed the code now...

Removed the MERGE statement.

Is this OK?

ALTER Trigger [dbo].[Trg_DeviationRequestDetails_Ins]

ON [dbo].[DeviationRequestDetails]

After INSERT AS

BEGIN

Declare @count INT

SELECT @count = COUNT(*) FROM INSERTED

If @count = 1

BEGIN

INSERT INTO CLCProcessUnitDetailsCompany

(

ProjectID,

ProposalID,

SolutionID,

UnitID,

DevDateChanged,

QuotDateChanged,

ApprovalDateChanged,

AddedBy,

DateAdded,

ChangedBy,

DateChanged

)

SELECT

S.ProjectID,

S.ProposalID,

S.SolutionID,

S.UnitID,

Getdate(),

NULL,

NULL,

S.AddedBy,

S.DateAdded,

S.ChangedBy,

S.DateChanged

FROM INSERTED S

JOIN CLCProcessUnitDetailsCompany T

ON S.ProjectID = T.ProjectID AND

S.ProposalID = T.ProposalID AND

S.SolutionId = T.SolutionId AND

S.UnitID = T.UnitID

END

ELSE

BEGIN

INSERT INTO CLCProcessUnitDetailsCompany

(

ProjectID,

ProposalID,

SolutionID,

UnitID,

DevDateChanged,

QuotDateChanged,

ApprovalDateChanged,

AddedBy,

DateAdded,

ChangedBy,

DateChanged

)

SELECT

TOP 1 S.ProjectID,

S.ProposalID,

S.SolutionID,

S.UnitID,

Getdate(),

NULL,

NULL,

S.AddedBy,

S.DateAdded,

S.ChangedBy,

S.DateChanged

FROM INSERTED S

JOIN CLCProcessUnitDetailsCompany T

ON S.ProjectID = T.ProjectID AND

S.ProposalID = T.ProposalID AND

S.SolutionId = T.SolutionId AND

S.UnitID = T.UnitID

ORDER BY S.DeviationStatus DESC

END

END