• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

SQL Trigger to update column with a value from another table

Hi experts! I need to have an Insert/update trigger that can upate a column in a table2 that is based on a value of a column from another table1.

Example:
Table1
CustID, Account#, name, etc.
Table2
CustID, Account#, Order, etc.

When a new record is inserted into table2, I need to get the CustID value in Table1 for the Account# that matches the Account# from the Inserted record.
0
sleiman
Asked:
sleiman
2 Solutions
 
EyalCommented:
alter TRIGGER trgTable2 
   ON  Table2
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @CustID int = 0
    select @CustID =CustID from inserted

-- do whatever you want with @CustID

GO

Open in new window

0
 
DerZaubererCommented:
CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[Table2]
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
IF @@ROWCOUNT <> 0
	IF UPDATE(Account#)
	BEGIN
		UPDATE Table2 SET Table2.CustID = Table1.CustID
		FROM Table2
		INNER JOIN INSERTED
		ON INSERTED.Account# = Table2.Account# AND INSERTED.[Order] = Table2.[Order]
		INNER JOIN Table1 
		ON Table1.Account# = Table2.Account#
	END
END

Open in new window

0
 
sleimanAuthor Commented:
Hi DerZauberer, it looks like solution cout work. Is there a way to use a nested select to only return one record value?

So, on each Insert, I need to get a unique Account# (single record) from Table1.

Table1 and Table2 have one to many relationship. So, table2 can have multiple records to be update.

Hope that makes sense.

 
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DerZaubererCommented:
This trigger will work even if you have inserted more than one row into Table2.
I don't know if you have a unique id on Table2 so i can exactly identify the inserted records.
That's why i used Account# and Order for the join on the "inserted" table to keep the number of affected rows as small as possible.
0
 
sleimanAuthor Commented:
I do have a recid that is unique for each record in table2.
I tried using recid instead of [order] (which I used as an example) and still not working.
Here is what I am using:

UPDATE Table2 SET Table2.CustID = Table1.CustID
      FROM Table2
      INNER JOIN INSERTED
      ON INSERTED.Account# = Table2.Account# AND INSERTED.RecID = Table2.RecID
      INNER JOIN Table1
      ON Table1.Account# = Table2.Account#

this is the error that I am getting.
The data in row 1 was not committed.
Error Source: .Net SqiClient Data Provider.
Error Message: Maximum stored procedure, Function, trigger, or
view nesting level exceeded (limit 32).

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
take this small change of the trigger:
CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[Table2]
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
IF @@ROWCOUNT <> 0
        IF UPDATE(Account#)
        BEGIN
                UPDATE Table2 SET Table2.CustID = Table1.CustID
                FROM Table2
                INNER JOIN INSERTED
                ON INSERTED.Account# = Table2.Account# AND INSERTED.[Order] = Table2.[Order]
                INNER JOIN Table1 
                ON Table1.Account# = Table2.Account#
             WHERE  ISNULL(Table2.CustID, 0) <> ISNULL(Table1.CustID,0)
        END
END

Open in new window

0
 
DerZaubererCommented:
Hmm, that's strange, i thought 'IF UPDATE(Account#)' should prevent recursive execution ...
Do you already have other triggers on the table that update Account#?
The suggested change of angelIII will make the trigger not work correctly on updates i think.

If you have unique id 'RecID' on the table join like this will do:
...
INNER JOIN INSERTED
ON INSERTED.RecID = Table2.RecID
...

It is possible to prevent recursion by additionally checking the nestlevel of a trigger like this:
...
IF @@NESTLEVEL <= 1
BEGIN
...
END
...

And i think the 'SET NOCOUNT ON' option from suggestion #1 is a good idea as well, as it might disturb some systems if there is more than one reply saying 'X rows affected.'. This option will prevent this. Add this right before the UPDATE-command.
0
 
sleimanAuthor Commented:
Well...some god new. I used the NOCOUNT and used the RecID in the join and now it works only for Insert action. If I try to update the record, it get the same error. My main goal was to handle inserted records and this should work. I am wondering though if we ever need to update the record, we will get the error.

I do have another tirgger that does a similar thing but updates other columns from table3 for example. I am not sure if that could be causing the error with the update. When I added a new rescord whiel testing, it seemed like both tirggers did what they were supposed to do.

I am wondering now if I should combine them.
Can the trigger hanled this by appeding the addition update code to the bottom of the existing trigger?
0
 
DerZaubererCommented:
You should be able to combine the triggers, but be aware of the fact that @@ROWCOUNT for example will be reset on each command within the trigger, so it only works on first line.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now