SQL Server Trigger for UPDATE

There should be no transaction conflict, the trigger is part of the same transaction.

Could you show us please the trigger code you use?

Disadvantages (Problems) of Triggers
http://blog.sqlauthority.com/2007/05/24/sql-server-disadvantages-problems-of-triggers/

Also most of the hosting company disable it.

Thank you for your answer.

I have no access to source code, so I need to implement this with a trigger on the database.

I haven't my code in front of me now, but I write something like this:

CREATE TRIGGER trig_dates ON myTable
AFTER UPDATE
IF UPDATED(B)
DECLARE
    CURSOR cInserted IS SELECT id, B FROM INSERTED WHERE A IS NULL;
BEGIN
   DECLARE @b datetime;
   DECLARE @id int;
 
   OPEN cInserted
   FETCH NEXT FROM cInserted INTO @id, @b;
 
   WHILE @@fetch_status = 0 BEGIN
      UPDATE myTable SET B=@b WHERE id=@id;
 
      FETCH NEXT FROM cInserted INTO @id, @a;
   END; 
   CLOSE cInserted;
END;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

I didn't know you can do a join inside an UPDATE.

I will try tomorrow and let you know. Regards.

You cannot. If you are using Oracle :-) This is "special syntax" of MSSQL.

There is a pretty good article for the join options you have with different databases at http:/articles/Database/Miscellaneous/UPDATES-with-JOIN-for-everybody.html (http:A_1517.html). I'm certain it contains some Oracle surprise for you, too. If you find it interesting, don't forget to vote Yes for the "Was this article helpful?".

Actually I'm using SQL-Server.

I mention Oracle because I have a lot of experience on Oracle, but The solution I were looking for was for SQL-Server, which I think you have provided it before.

I'll try tomorrow and let you know.

Regards.

... and there the Article author went in  ^^^ ...

I know that we are talking about MSSQL. I just wanted to point out the difference between both, and that Oracle allows for a similar syntax (but not the same).
Happy trying ...

Just a very minor change (no points please), just to allow for the time when B does not change:

CREATE TRIGGER trig_dates ON myTable

AFTER UPDATE

AS

IF UPDATED(B)
    BEGIN
        UPDATE  tbl
        SET     A = new.B
        FROM    INSERTED new
                JOIN myTable tbl ON new.id = tbl.id
                JOIN DELETED old ON new.id = old.id
        WHERE   new.A IS NULL
                        AND new.B <> Old.B
    END


If there is any chance that the new or old B could be Null then change:
AND new.B <> Old.B

To:
(AND new.B <> Old.B
Or (new.B IS NULL And old.B IS NOT NULL)
Or (new.B IS NOT NULL And old.B IS NULL))

Thank you.

That's true, acp. Totally overseen ... Under rare conditions, this might be an issue.

Why we need that? IF UPDATED(B) is triggered if the column is set in an UPDATE, even if it is unchanged or empty.  As long as it remains empty (NULL), no harm would be done; but if B is filled already without triggering the code (how ever this should happen), at first update of the table with B in SET the value would be copied into A. Whether that is a bug or a feature is left to the question author.

Sorry, I think now I don't fully understand. Which bug ? Does this bug invalidates the second code or the first one ?

>>Why we need that? IF UPDATED(B) is triggered if the column is set in an UPDATE, even if it is unchanged or empty.<<
That was to account for the time when B is updated, but not changed.  But you are right that is for the author to decide if it is relevant or not.

Yes, it could be rellevant, as maybe the program is updating the field even when there is no change (maybe is updating all fields for any change).

What I don't understand is the comment abut the bug.

As long as B is NULL, that value will be updated for A each time you update your table. I.e. the trigger is writing NULL into A each time.

Is there any situation were B could be filled, but with the trigger disabled temporarily, e.g. bulk inserts/copys or the like? Or records inserted with a already filled-out B?

no, there aren't bulk inserts or something like that. So once the trigger is created, it won't be dsabled.

Just forget about the specific case of NULL for now.  If you only want to update A when B's value actually changes then you have to compare the before and after values as I posted.  All IF UPDATED(B) tells you is that B participated in an UPDATE statement and not that its value changed.

I trust that is clear.

Excellent. Now I understand. Thank you very much.

So I wil add the conditions on the WHERE.

Excellent. It worked properly now.

Can you explain why my own solution failed ? Is it not possible to query the UPDATED trigger using a cursor inside the trigger?

>> "Can you explain why my own solution failed"
No, I cannot. Even if using a Cursor, it should have worked.