How to create a insert trigger on just one table?

Member236

I have a table product, columns:id,date,product,productname I want to create a insert trigger, when I insert id,date,product, the productname will auto update in to the table(the product=productname... I don't know how to make the equation, please help me. sorry for my terrible English.

CHill60

Why have two columns with the same data? Have you tried anything at all yet?

Richard Deeming

CREATE TRIGGER (Transact-SQL) | Microsoft Docs[^]

CREATE TRIGGER YourTriggerName
ON YourTableName
AFTER insert, update
As
BEGIN
SET NOCOUNT ON;

If UPDATE(FK)
BEGIN
    UPDATE
        T
    SET
        RedundantDuplicateColumn = T2.Name
    FROM
        YourTable As T
        INNER JOIN inserted As I
        ON I.PK = T.PK
        LEFT JOIN YourOtherTable As T2
        ON T2.PK = T.FK
    ;
END;

END

Replace the dummy table and column names with the real table and column names. Remember that triggers will fire once per batch, not once per row. There could be multiple rows in the inserted table. But as CHill60 said, duplicating the product name in both tables seems like a bad design. If you have the ID of the product, you can simply join to the product table to get the name on demand.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mycroft Holmes

This is a REALLY BAD design, instead of having a separate table for productname create a view

create view vwProductName
as
select distinct product as productname from product

Never underestimate the power of human stupidity RAH

CHill60

Am I missing something ... where's the table for the ProductName mentioned?

Member236

It's an ERP bill, customer want to input the batch number, but they don't want to use any rules, just want to fetch the bill number into that field.

Mycroft Holmes

OP wrote:

the productname will auto update in to the table(the product=productname.

So yes I think you missed this.

Never underestimate the power of human stupidity RAH

Mycroft Holmes

The comments still stand - there should be no reason to store the productname in a different table especially as it is user input and free form.

Never underestimate the power of human stupidity RAH