Check Issue_ID value, before insertion

Question

I have #tbl_GRN_Detail in which issue_ID col exit,i want when i insert row into table #tbl_issuance ,it check in #tbl_GRN_Detail that Issue_ID value is null then insert into #tbl_issuance other not.

i am using trigger for Updating issue_ID in #tbl_issuance from table of #tbl_GRN_DEtail.

I tried to used IF NOT EXISTS in SP ,but it is not working properly

Create table #tbl_GRN_Detail (D_ID int,GRN_ID int,item_code int,Issue_ID int)
 Insert into #tbl_GRN_Detail values (11001,2001,22,Null)


Create table #tbl_issuance  (issue_ID int,item_code int,Issue_Date int,D_ID int)

    IF NOT  EXISTS(Select Issue_ID from tbl_GRN_Detail 
    where Issue_ID=@Issue_ID  and Issue_ID is not null)
    begin

     Insert into #tbl_issuance values (101,22,'2021-06-28',11001)
     end



CREATE TRIGGER issuance_tri ON tbl_issuance AFTER INSERT AS
UPDATE tbl_GRN_Detail
SET    Issue_ID = i.issue_ID
FROM   tbl_GRN_Detail G
JOIN   inserted i ON G.D_ID = i.D_ID
go

Answer 1

Hi @Analyst_SQL ,

Sorry that I am confused.

I also tried below when #tbl_Grn_Detail Col Issue_ID is not null, tbl_issuance is not inserted:

drop table if exists tbl_GRN_Detail,tbl_issuance  
      
Create table tbl_GRN_Detail (D_ID int,GRN_ID int,item_code int,Issue_ID int)  
Insert into tbl_GRN_Detail values (11001,2001,22,1)  
            
Create table tbl_issuance  (issue_ID int,item_code int,Issue_Date date,D_ID int)  
GO  
  
DECLARE @D_ID int=11001  
DECLARE @Issue_ID INT=101  
  
IF NOT EXISTS(Select 1 from tbl_GRN_Detail where D_ID=@D_ID  and Issue_ID is NOT null)  
begin          
  Insert into tbl_issuance values (@Issue_ID,22,'2021-06-28',@D_ID)  
end  
  
select * from tbl_GRN_Detail  
select * from tbl_issuance  

i am saying ,when #tbl_Grn_Detail Col Issue_ID is not null ,then row should not insert into table #tbl_Issuance

Does above also mean that only when #tbl_Grn_Detail Col Issue_ID is null, then #tbl_Issuance could be inserted?

It could be better to provide some sample data with both conditions(should not insert and should insert) so that I could understand better.

Thank you for understanding!

Best regards,
Melissa

Answer 2

Hi @Analyst_SQL ,

Please refer below updated one and check whether it is working.

drop table if exists tbl_GRN_Detail,tbl_issuance  
      
Create table tbl_GRN_Detail (D_ID int,GRN_ID int,item_code int,Issue_ID int)  
Insert into tbl_GRN_Detail values (11001,2001,22,Null)  
            
Create table tbl_issuance  (issue_ID int,item_code int,Issue_Date date,D_ID int)  
GO  
  
CREATE TRIGGER issuance_tri ON tbl_issuance   
AFTER INSERT   
AS  
UPDATE G  
SET    G.Issue_ID = i.issue_ID  
FROM   tbl_GRN_Detail G  
JOIN   inserted i ON G.D_ID = i.D_ID  
GO  
  
DECLARE @D_ID int=11001  
DECLARE @Issue_ID INT=101  
  
IF EXISTS(Select Issue_ID from tbl_GRN_Detail where D_ID=@D_ID  and Issue_ID is null)  
begin          
  Insert into tbl_issuance values (@Issue_ID,22,'2021-06-28',@D_ID)  
end  
        
select * from tbl_GRN_Detail  
select * from tbl_issuance  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Analyst_SQL ，

Please refer below:

drop table if exists tbl_GRN_Detail,tbl_issuance  
      
Create table tbl_GRN_Detail (D_ID int,GRN_ID int,item_code int,Issue_ID int)  
Insert into tbl_GRN_Detail values (11001,2001,22,Null)  
            
Create table tbl_issuance  (issue_ID int,item_code int,Issue_Date date,D_ID int)  
GO  
  
CREATE TRIGGER issuance_tri ON tbl_issuance   
AFTER INSERT   
AS  
UPDATE G  
SET    G.Issue_ID = i.issue_ID  
FROM   tbl_GRN_Detail G  
JOIN   inserted i ON G.D_ID = i.D_ID  
WHERE G.Issue_ID IS NULL  
GO  
  
Insert into tbl_issuance values (101,22,'2021-06-28',11001)  
          
select * from tbl_GRN_Detail  
select * from tbl_issuance  

If above is not working, please provide more sample data or more details together with expected output.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.