locked
Update Trigger. RRS feed

  • Question

  • User-614943948 posted

    I am new to triggers, I have a table named "tbl_Issueform" containing a column named "Qty".

    I have a transaction table like this. 

    CREATE TABLE tbl_ItemTransaction
    (
    	ÍD			INT IDENTITY(1,1),
    	PartID			INT,
    	OpeningBal		INT,
    	ClosingBal		INT,
    	RenewDate		DateTime,
    )

    I want to fire a trigger, which should upgrade the Value of ClosingBal when I Insert something into values inside the table "tbl_Issueform". How can i accomplish this?

    Thursday, November 15, 2018 9:47 AM

Answers

  • User-2082239438 posted

    maverick786us

    I am new to triggers, I have a table named "tbl_Issueform" containing a column named "Qty".

    I have a transaction table like this. 

    CREATE TABLE tbl_ItemTransaction
    (
    	ÍD			INT IDENTITY(1,1),
    	PartID			INT,
    	OpeningBal		INT,
    	ClosingBal		INT,
    	RenewDate		DateTime,
    )

    I want to fire a trigger, which should upgrade the Value of ClosingBal when I Insert something into values inside the table "tbl_Issueform". How can i accomplish this?

    When you have multiple rows operation (it should), you code will work for only one row and it is not right. 

    Try to think about your issue again to see whether you can solve your problem without a trigger. If you do want to use trigger, use join with inserted/deleted tables to process, get rid of these variables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 15, 2018 11:07 AM
  • User-893317190 posted

    Hi maverick786us,

    Because it is not clear what you want to do use  trigger.

    I make a simple trigger.

    Below is my table

      create table tbl_issueForm
      (
      id int identity(1,1) primary key,
      pname varchar(200),
      qty int,
      price float
      )
    
    
    create table tbl_ItemTransaction(
       id int identity(1,1) primary key,
       partId int,
       OpeningBal int,
       ClosingBal int,
       RenewDate DateTime
    )
    

    And my trigger.

    create  trigger trig_insert_Issueform  --trigger name
    on tbl_Issueform  -- on which table you want to fire the trigger
    after insert  -- when you want to fire the trigger you could also use after update when the table is updated the trigger will be fired
    as
    begin
      insert into tbl_ItemTransaction(partId,ClosingBal,RenewDate)
    
       select id,qty, getDate() from inserted  --in the trigger you could visit the temp table inserted where the newly inserted date is stored 
      
    end

    If it is not your case ,please specify how you want to upgrade the value ClosingBal, since it is not clear about the relationship between the table tbl_itemTransaction and tbl_issueform  and how you want to upgrade table tbl_itemTransaction  with the column QTY in tbl_issueform ,

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 16, 2018 5:19 AM

All replies

  • User-2082239438 posted

    maverick786us

    I am new to triggers, I have a table named "tbl_Issueform" containing a column named "Qty".

    I have a transaction table like this. 

    CREATE TABLE tbl_ItemTransaction
    (
    	ÍD			INT IDENTITY(1,1),
    	PartID			INT,
    	OpeningBal		INT,
    	ClosingBal		INT,
    	RenewDate		DateTime,
    )

    I want to fire a trigger, which should upgrade the Value of ClosingBal when I Insert something into values inside the table "tbl_Issueform". How can i accomplish this?

    When you have multiple rows operation (it should), you code will work for only one row and it is not right. 

    Try to think about your issue again to see whether you can solve your problem without a trigger. If you do want to use trigger, use join with inserted/deleted tables to process, get rid of these variables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 15, 2018 11:07 AM
  • User-893317190 posted

    Hi maverick786us,

    Because it is not clear what you want to do use  trigger.

    I make a simple trigger.

    Below is my table

      create table tbl_issueForm
      (
      id int identity(1,1) primary key,
      pname varchar(200),
      qty int,
      price float
      )
    
    
    create table tbl_ItemTransaction(
       id int identity(1,1) primary key,
       partId int,
       OpeningBal int,
       ClosingBal int,
       RenewDate DateTime
    )
    

    And my trigger.

    create  trigger trig_insert_Issueform  --trigger name
    on tbl_Issueform  -- on which table you want to fire the trigger
    after insert  -- when you want to fire the trigger you could also use after update when the table is updated the trigger will be fired
    as
    begin
      insert into tbl_ItemTransaction(partId,ClosingBal,RenewDate)
    
       select id,qty, getDate() from inserted  --in the trigger you could visit the temp table inserted where the newly inserted date is stored 
      
    end

    If it is not your case ,please specify how you want to upgrade the value ClosingBal, since it is not clear about the relationship between the table tbl_itemTransaction and tbl_issueform  and how you want to upgrade table tbl_itemTransaction  with the column QTY in tbl_issueform ,

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 16, 2018 5:19 AM