After insert trigger

Transact-SQL
1

here are the tables

CREATE TABLE #data
(
ProductCode varchar(100) ,
LOT varchar(100),
BIN VARCHAR(100)
Hold varchar(100),
Narrations varchar(100)

)

go

insert into #data select 'Prod1', 'LOT1','01', 'Y', 'QC'
insert into #data select 'PROD1','LOT2','01' 'Y','QC'
insert into #data select 'PROD1','LOT5','02''Y''QC'
insert into #data select 'PROD1','LOT10', '03','Y','QC'

When inserting a new line, if HOLD = 'Y' then i need to CHECK IF NOTE UPDATE APPLIES FOR INSERTED RECORD. The inserted record always has Note field empty.

If same record exists? with the same Product Code, Lot (Bin will not match) Bin could be PROD or QA and Note = QC then if found
update inserted record Note = QC as well.

2

Can you explain a little more? What does I need to CHECK IF NOTE UPDATE APPLIES FOR INSERTED RECORD mean?

3

if same Product Code and Lot exits and if it's Hold is Y and Note is QC.

then update note to QC.

4

This is still Confusing. Are you saying when you insert a record, if Hold = 'Y' then the Note should be 'QC' else null??? Is the Note Column part of the table?

For instance,

insert into #data select 'Prod1', 'LOT1','01', 'Y', 'QC' <-- this would put QC in the note column
insert into #data select 'PROD1','LOT2','01', 'N','QC' <-- this would leave note column null

5

if I am inserting a new Line:
Prod1, LOT1, 03, Y, ' '

and the following record exist:
Prod1, LOT1, 01, Y, QC

Then the inserted line should have Narration updated to QC.

6

Upsert, merge

7

here's how to do it with a trigger, but I'd be concerned if there is alot of data hitting this. Can it be done ahead of time? Also, I added identity column so I had something to join on to get the record I just inserted.

drop table if exists data
go

CREATE TABLE data
(
id int identity(1,1),
ProductCode varchar(100) ,
LOT varchar(100),
BIN VARCHAR(100),
Hold varchar(100),
Narrations varchar(100)

)

go

CREATE TRIGGER data_trg ON data
AFTER INSERT
AS
IF EXISTS (SELECT 1
           FROM data p 
           JOIN inserted AS i 
		   on p.ProductCode = i.ProductCode
           and p.LOT = i.LOT
		   and p.ID <> i.id)

BEGIN
	update d
	   set Narrations = 'QC'
	  from data d
		join inserted i
			 on d.id = i.ID
END;
go


insert into data select 'Prod1', 'LOT1','01', 'Y', 'QC'
insert into data select 'PROD1','LOT2','01', 'Y','QC'
insert into data select 'PROD1','LOT5','02','Y','QC'
insert into data select 'PROD1','LOT10', '03','Y','QC'
go

select * from data

insert into data select 'Prod1', 'LOT1','03', 'Y', ''

select * from data

insert into data select 'Prod1', 'LOT3','03', 'Y', ''

select * from data

insert into data select 'Prod1', 'LOT3','03', 'Y', null

select * from data
1 Like