Trigger updating same table

Transact-SQL
1

Can somebody please help with this -

I have 2 tables -
PLC Data, DataLookup

I need a trigger when I insert a value in PLC Data table, to match a value in Datalookup table and write to the PLC Data table.

Eg -

If I insert BarCodeVal = '1000' in the PLC Data table, the trigger will match the barcode value from the DataLookUp table as an example - '0' and write back to the PLC Data table.

I am inserting 1000 to the PLC Data table
The end result will be
03-10-2020 12:00 1000 0

I am inserting 1001 to the PLC Data table
The end result will be
03-10-2020 12:00 1000 1

CREATE TABLE [dbo].[PLC Data](
[DateStamp] [DateTime], NULL
[BarCodeVal] varchar NULL,
[LocVal] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DataLookUp](
[Lk_BarCode] varchar NULL,
[Lk_Location] [int] NULL
) ON [PRIMARY]
GO

DATA -
insert into LookUp
Values ('1000', '0')

insert into LookUp
Values ('1001', '1')

insert into LookUp
Values ('1002', '2')

insert into LookUp
Values ('1003', '3')

insert into LookUp
Values ('1004', '4')

insert into LookUp
Values ('1005', '5')

I am using SQL Server 2017.

Thanks.

2 
CREATE TRIGGER [plc data__trg_INSERT]
ON dbo.[plc data]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE pd
SET locval = dl.lk_location
FROM dbo.[plc data] pd
INNER JOIN inserted i ON i.barcodeval = pd.barcodeval
INNER JOIN dbo.datalookup dl ON dl.lk_barcode = i.barcodeval
GO

INSERT INTO [plc data] (barcodeval) VALUES('1000')
INSERT INTO [plc data] (barcodeval) VALUES('1001')

SELECT * FROM [plc data]
1 Like
3

Thanks Scott !

That worked. Can I also get the date stamp inserted in tot the PLCData table at the same time ?

4

Sure, assuming you mean UPDATEd rather then "inserted".

CREATE TRIGGER [plc data__trg_INSERT]
ON dbo.[plc data]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE pd
SET 
    datestamp = GETDATE(),
    locval = dl.lk_location
FROM dbo.[plc data] pd
INNER JOIN inserted i ON i.barcodeval = pd.barcodeval
INNER JOIN dbo.datalookup dl ON dl.lk_barcode = i.barcodeval
GO