0

I need help on SQL Server syntax to create some triggres that I´ve used with success on SQLite, but I'm having trouble with NEW.Table once it´s not part of SQL Server.

That´s my SQLite trigger code:

UPDATE EngineeringItems set ElevacaoFIT = CAST(round((select "Position Z" - MatchingPipeOD / 2 from EngineeringItems where PnPId = new.PNPID), 0) as INT) where PNPID = new.PNPID;

PNPID is the table pk!

EDIT 2:

Thanks again buddy! I´ve tried your new code with a little modifications and SQL Server accepted it and trigger was created successfully. So the code is:

CREATE TRIGGER [ElevacaoFIT_Insert]
   ON  [dbo].[EngineeringItems]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE EngineeringItems
SET
    ElevacaoFIT = CAST(ROUND((select EngineeringItems."Position Z" - (EngineeringItems.MatchingPipeOD / 2)
FROM
    EngineeringItems INNER JOIN inserted ON EngineeringItems.PnPId = inserted.PnPId), 0) AS INT);
END

But now I get this message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.! Any idea?

Sorry for bother you... Thanks again!!

user2083234
  • 211
  • 2
  • 4

1 Answers1

1

Can you post what you have tried on SQL Server?

Something like the following may do it:

CREATE TRIGGER [dbo].[EngineeringItems_Trigger] 
   ON  [dbo].[EngineeringItems]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   UPDATE EngineeringItems
   SET
     ElevacaoFIT = CAST(round(("Position Z" - MatchingPipeOD / 2), 0) as INT)
   FROM
     EngineeringItems INNER JOIN inserted ON EngineeringItems.PnPId = inserted.PNPID;
END
youcantseeme
  • 21
  • 3