I've the following tables:
table1 - LotTransactions: StockCode, BinNumber, LOT, Date , PO, Source
Once new record is added I need to update lot number which would equal existing lot number + 4 random digits.
This update should only happen if Source is P.
table2 - LOTDetails: StockCode, BinNumber, LOT
Table2 LOT needs to be also updated by adding the same 4 digits. as in table1.
For example:
Transaction table has this records after insert
product10 , bin02, lot100, currentdate, PO567, P
LOTDetails table
product10, lot100, qty, bin02
After Trigger it should look like below:
Transaction Table - product10 , bin02, lot100.6548, currentdate, PO567, P
Lot Detail Table - product10, lot100.6548, qty, bin02
Don't do this.
But if you must something like this
You need to consider the unique indexes on tjhe tables and bear in mind that a random number isn't unique.
This is a really bad idea.
drop table [transaction]
go
drop table LOTDetails
go
create table [transaction] (StockCode varchar(100), LOT varchar(100), Source varchar(1))
go
create table LOTDetails (StockCode varchar(100), LOT varchar(100))
go
create trigger tr_transaction on [transaction] for insert
as
declare @a table (StockCode varchar(20), Lot varchar(100))
insert @a select StockCode, LOT+'.'+right(convert(varchar(100),checksum(newid())),4)
from inserted
where Source = 'P'
update [transaction]
set Lot = t.Lot
from [transaction] f
join @a t
on f.StockCode = t.StockCode
update LOTDetails
set Lot = t.Lot
from LOTDetails f
join @a t
on f.StockCode = t.StockCode
go
insert LOTDetails select 'a', ''
insert LOTDetails select 'b', ''
insert LOTDetails select 'c', ''
select * from [transaction]
select * from LOTDetails
insert [transaction] select 'a', 'a', 'P' union all select 'b', 'b', 'P' union all select 'c','c','X'
select * from [transaction]
select * from LOTDetails