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

Select allOpen in new window

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.

 

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.

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).

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.

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?

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.