I have two tables, first with parts, second with orders. In orders i store IDPart and quantity of this part. I would like achieve that when inserting the IDPart and Quantity values into the Orders, the trigger automatically fires up to calculate the price. Did someone can help to figure it out?

Sample:

CREATE TABLE Parts
(
    ID INT IDENTITY(1,1) PRIMARY KEY,               
    PartName NVARCHAR(70),
    Price DECIMAL(6,2)
)

CREATE TABLE Orders
(
    ID INT IDENTITY(1,1) PRIMARY KEY,   
    IDPart INT REFERENCES Parts(ID),
    Quantity INT,
    Price DECIMAL(6,2)
)

CodePudding user response：

As you mentioned, what you want to do is:

Create a trigger
Establish when it should trigger - Insert & update
Create a function which runs each time an update or insertion is made

Please be aware that I am only experienced with PL/pgSQL (PostgresSQL), so that's the language of the function.

Creating a trigger

CREATE TRIGGER insertOrder    
AFTER INSERT OR UPDATE ON Orders
FOR EACH ROW
EXECUTE PROCEDURE functionSetOrderPrice();

This trigger is called insertOrder, and will trigger the function "functionSetOrderPrice()" whenever we insert or update a row in the Orders-table.

Creating the function

CREATE FUNCTION functionSetOrderPrice()
RETURNS trigger AS 
$$
BEGIN
  -- Getting the particular part's price
  part_price := (SELECT price FROM Parts WHERE Parts.ID=NEW.IDPart);

  -- Multiplying the price with the quantity to get order total price
  order_price := part_price * NEW.quantity;

  -- Let's set the Orders.price to the correct value!
  NEW.price = order_price;
  return NEW;
END;
$$ LANGUAGE plpgsql;

Some things to be mindful of, and can help you and others understand your database is to stick to these principles:

Table-names should always be plural and capitalized: Parts, Orders, Users
Attribute-names should always be lower-case and singular: quantity, name, id

Also, if you were looking for triggers for MySQL, the syntax is slightly different, but the thought-process is the same. Here's a good example.