SQL Server Insert values in second table using triggers

anhgbhbe 于 2023-04-10 发布在其他

关注(0)|答案(3)|浏览(121)

I have one table called [FridgeTemperture] , when any record inserted it should add one value in the new table MpSensors . But records are not being inserted in the new table when a record is inserted.

Error
Explicit value must be specified for identity column in table 'MpSensors' either identity_insert is set to ON or when a replication user is inserting into a not for replication identity column.

CREATE TRIGGER [dbo].[FridgeTemperature_INSERT]
    ON [dbo].[FridgeTemperture]
    AFTER INSERT
AS
BEGIN
    SET IDENTITY_INSERT MpSensors ON;

    SET NOCOUNT ON;

    DECLARE @fridge_temp varchar(10)

    INSERT INTO MpSensors(fridge_temp)
        VALUES(@fridge_temp)

    SET IDENTITY_INSERT MpSensors OFF;
END    
GO

Table schema

CREATE TABLE [dbo].[MpSensors](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fridge_temp] [varchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[FridgeTemperture](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ShopId] [nvarchar](4) NULL,
    [Fridgetemp] [decimal](4, 2) NOT NULL,
    [UpdatedDate] [datetime2](7) NOT NULL
)
GO

sql-server

来源：https://stackoverflow.com/questions/56438196/insert-values-in-second-table-using-triggers

3条答案

按热度按时间

zy1mlcev1#

You don't need the set identity_insert on if you are not attempting to insert values to the identity column. Also, your current insert statement, if you loose the set identity_insert , will simply inside a single null row for any insert statement completed successfully on the FridgeTemperture table.
When using triggers, you have access to the records effected by the statement that fired the trigger via the auto-generated tables called inserted and deleted .
I think you are after something like this:

CREATE TRIGGER [dbo].[FridgeTemperature_INSERT]
   ON  [dbo].[FridgeTemperture]
   AFTER  INSERT
AS 
BEGIN

   INSERT INTO MpSensors(fridge_temp)
   SELECT CAST(Fridgetemp as varchar(10)) 
   FROM inserted

END

Though I can't really see any benefit of storing the same value in two different places, and in two different data types.

Update

Following our conversation in the comments, you can simply use an update statement in the trigger instead of an insert statement:

UPDATE MpSensors
SET fridge_temp = (
    SELECT TOP 1 CAST(Fridgetemp as varchar(10)) 
    FROM inserted
    ORDER BY Id DESC
)

This should give you the latest record in case you have an insert statement that inserts more than a single record into the FridgeTemperture table in a single statement.

赞(0）回复(0）举报 2023-04-10

zujrkrfu2#

You need to use SELECT statement with CAST as [fridge_temp] is varchar in MpSensors table in Trigger. Try like this:

CREATE TRIGGER <table_name>
   ON <table_name>
   AFTER Insert
AS 
BEGIN    
   INSERT INTO <table_name>(column_name)
   SELECT CAST(column_name AS varchar(10)) 
   FROM inserted    
END

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

赞(0）回复(0）举报 2023-04-10

m1m5dgzv3#

CREATE TRIGGER [dbo].[FridgeTemperature_INSERT]
   ON [dbo].[FridgeTemperture]
   AFTER INSERT
AS 
BEGIN
  UPDATE MpSensors
  SET fridge_temp = CAST(Fridgetemp as varchar(10)) 
  FROM inserted
END

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server Insert values in second table using triggers

3条答案

相关问题

热门标签

最新问答