question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft answered

Check Issue_ID value, before insertion

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
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @akhterhussain-3167,

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

109764-output.png

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





output.png (2.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167

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

109610-output.png

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.


output.png (3.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft

I am saying that,when #tbl_GRN_Detail col Issue_ID is not null ,then row should not insert into #tbl_Issuance table ,means that query check before inserting data into #tbl_Issuance ,that #tbl_GRN_Detail col issue_id is null ,then allow to insert into #tbl_issuance otherwise not allowed.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167,

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

109697-output.png

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.


output.png (3.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft

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

0 Votes 0 ·