I am creating a new trigger for whatever action performed on master table, I need to update my local table using trigger for Insert,Update and Delete. But now I am facing issue during multiple row insert or update.
I had created trigger but it's not working properly for multiple row insertion and update.
CREATE TRIGGER [dbo].[TRG_pspartmas] ON [dbo].[ps_partmas]
AFTER INSERT
,UPDATE
,DELETE
AS
BEGIN
DECLARE @cmp_id VARCHAR(10)
DECLARE @sl_type VARCHAR(10)
DECLARE @sl_code VARCHAR(20)
DECLARE @sl_desc VARCHAR(200)
DECLARE @region_code VARCHAR(10)
DECLARE @tranYN VARCHAR(10)
DECLARE @IT_NO VARCHAR(50)
------------------------------------------------------------------------------Fetching recored from inserted
-- SELECT @cmp_id=cmp_id,@sl_type=cl_supp_code,@sl_code=part_code,@sl_desc=part_nm,@region_code=region_code,@tranYN=Stop_Order,@it_NO=IT_no
-- FROM inserted
--------------------------------------------------------------------------------if recored were deletd
SELECT @cmp_id = rtrim(ltrim(cmp_id))
,@sl_code = rtrim(ltrim(part_code))
,@sl_desc = rtrim(ltrim(part_nm)) --,@tranYN=rtrim(ltrim(Stop_Order))
FROM deleted
IF EXISTS (
SELECT *
FROM view_sl_code1
WHERE cmp_id = rtrim(ltrim(@cmp_id))
AND sl_code = rtrim(ltrim(@sl_code))
AND sl_type = @sl_type
)
BEGIN
BEGIN
DELETE
FROM view_sl_code1
WHERE cmp_id = rtrim(ltrim(@cmp_id))
AND sl_code = rtrim(ltrim(@sl_code))
AND sl_type = rtrim(ltrim(@sl_type))
END
END
SELECT @cmp_id = cmp_id
,@sl_type = cl_supp_code
,@sl_code = part_code
,@sl_desc = part_nm
,@region_code = region_code
,@tranYN = Stop_Order
,@it_NO = IT_no
FROM inserted
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF EXISTS (
SELECT *
FROM view_sl_code1
WHERE cmp_id = rtrim(ltrim(@cmp_id))
AND sl_code = rtrim(ltrim(@sl_code))
AND sl_type = @sl_type
)
BEGIN
UPDATE view_sl_code1
SET sl_desc = rtrim(ltrim(@sl_desc))
,tranYN = rtrim(ltrim(@tranYN))
,actions = 'partmas_up'
,date_update = GETDATE()
WHERE cmp_id = rtrim(ltrim(@cmp_id))
AND sl_code = rtrim(ltrim(@sl_code))
END
------------------------------------------------------------------------------inserting new recored
IF NOT EXISTS (
SELECT *
FROM view_sl_code1
WHERE cmp_id = rtrim(ltrim(@cmp_id))
AND sl_code = rtrim(ltrim(@sl_code))
AND sl_type = @sl_type
)
BEGIN
INSERT INTO view_sl_code1 (
cmp_id
,sl_type
,sl_code
,sl_desc
,region_code
,tranYN
,IT_NO
,actions
,date_update
)
SELECT rtrim(ltrim(cmp_id))
,rtrim(ltrim(cl_supp_code)) AS sl_type
,rtrim(ltrim(part_code)) AS sl_code
,rtrim(ltrim(part_nm)) AS sl_desc
,rtrim(ltrim(region_code))
,CASE
WHEN Stop_Order = 'Y'
THEN 'N'
ELSE 'Y'
END AS tranYN
,IT_NO
,'partmas_in'
,getdate()
FROM dbo.ps_partmas
WHERE cmp_id = @cmp_id
AND part_code = @sl_code
AND cl_supp_code = @sl_type
END
--end
PRINT 'Trigger is activated'
END
I need to capture all the inserted and updated values in my local table.
Could you please suggest?
You need to simply join to the inserted and deleted tables.
Here is an example. I have removed all the ltrim/rtrim for readability, add it back if you need it.
delete view_sl_code1
from view_sl_code1
join deleted
on deleted.cmp_id=view_sl_code1.cmp_id
and deleted.sl_code=view_sl_code1.sl_code
and deleted.sl_desc=view_sl_code1.sl_desc
update view_sl_code1
SET sl_desc = inserted.sl_desc
,tranYN = inserted.tranYN
,actions = 'partmas_up'
,date_update = GETDATE()
from view_sl_code1
join inserted
on inserted.cmp_id = view_sl_code1.cmp_id
AND inserted.sl_code = view_sl_code1.sl_code
insert view_sl_code1 (
cmp_id
,sl_type
,sl_code
,sl_desc
,region_code
,tranYN
,IT_NO
,actions
,date_update
)
select cmp_id,
cl_supp_code AS sl_type
,part_code AS sl_code
,part_nm AS sl_desc
,region_code
,CASE
WHEN Stop_Order = 'Y'
THEN 'N'
ELSE 'Y'
END AS tranYN
,IT_NO
,'partmas_in'
,getdate()
FROM dbo.ps_partmas
join inserted
on inserted.cmp_id = ps_partmas.cmp_id
AND inserted.part_code = ps_partmas.sl_code
AND inserted.cl_supp_code = ps_partmas.sl_type
where not exists(
SELECT *
FROM view_sl_code1
join inserted
on inserted.cmp_id = view_sl_code1.cmp_id
AND inserted.sl_code = view_sl_code1.sl_code
AND inserted.sl_type = view_sl_code1.sl_type
)