Search code examples
sql-serverdatabasetriggerssubquery

Using trigger to insert automatically from other table

I need to create trigger in a SQL Server database so that after the user inserts a row in the table it will automatically import the bill number from another table by bill Account in both tables and insert it in userCode column in my current table, my query is:

ALTER TRIGGER [dbo].[payTrigger]
ON [dbo].[Payment] AFTER INSERT As 
BEGIN
    update Payment
        set userCode = (
            select Bill.userCode 
            from Bill
            inner join Payment on Bill.BillAccount = Payment.BillAccount 
        )

The issue is: the query returns all column, how to let it import specific field regarding to userCode, when I try to insert new row, this error appears:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Solution

    1. There is a lot of different userCode values matched the same BillAccount in Bill table. You must select one of them by some criteria - for example, you may get minimal using select MIN(Bill.userCode) in subquery.

    2. For to update newly inserted row only you must add WHERE Payment.ID = INSERTED.ID - without it all table rows will be updated.