Sample code for a trigger in SQL

I'm not able to test it but give this a try:

CREATE TRIGGER trg_MyTableUpdate 
ON MyTableName
FOR UPDATE   
AS   
    UPDATE Inserted 
    SET ColumnC = 'C' 
    WHERE UPDATE(ColumnA) = 1 AND UPDATE(ColumnB)  = 1
GO  

Select allOpen in new window

THE ABOVE WILL NOT WORK since we are not updating user's table,  Also there is no where clause etc..

Please try this full tested solution.

--

CREATE TABLE YourTableNmT
(
	 Id INT
	,ColumnA VARCHAR(1)
	,ColumnB VARCHAR(1)
	,ColumnC VARCHAR(1)
)
GO

INSERT INTO YourTableNmT VALUES
(1,'x','t','u'),
(2,'y','s','v'),
(3,'z','r','w'),
(4,'w','q','m'),
(5,'t','p','n'),
(5,'A','B','o')
GO

--

Select allOpen in new window

TRIGGER SOLUTION CODE

--

CREATE TRIGGER trg_YourTableNmT 
ON YourTableNmT
FOR UPDATE   
AS   
    UPDATE T
	SET T.ColumnC = 'C' 
	FROM YourTableNmT T
	INNER JOIN inserted i ON T.Id = i.Id    
    WHERE I.ColumnA = 'A' AND I.ColumnB = 'B'
GO 

Select allOpen in new window

TESTING SCENARIOS..

/*TEST 1 */

-- WHEN WE UPDATE THE ColumnA = 'A' , ColumnB = 'B' , IN THIS CASE ColumnC should be updated to 'C'

--

UPDATE YourTableNmT 
SET ColumnA = 'A' , ColumnB = 'B'
WHERE Id = 1

SELECT * FROM YourTableNmT

--
[code]

OUTPUT

[code]

/*------------------------
SELECT * FROM YourTableNmT
------------------------*/
Id          ColumnA ColumnB ColumnC
----------- ------- ------- -------
1           A       B       C
2           y       s       v
3           z       r       w
4           w       q       m
5           t       p       n
5           A       B       o

(6 row(s) affected)

Select allOpen in new window

/*TEST 2 */

-- WHEN WE UPDATE ONLY ColumnA = 'A' AND NOT ColumnB , IN THIS CASE ColumnC should NOT be updated to 'C'

--

UPDATE YourTableNmT 
SET ColumnA = 'A' 
WHERE Id = 2

SELECT * FROM YourTableNmT

--
[code]

OUTPUT

[code]

/*------------------------
SELECT * FROM YourTableNmT
------------------------*/
Id          ColumnA ColumnB ColumnC
----------- ------- ------- -------
1           A       B       C
2           A       s       v
3           z       r       w
4           w       q       m
5           t       p       n
5           A       B       o

(6 row(s) affected)

Select allOpen in new window

/*TEST 2 */

-- WHEN WE UPDATE ONLY ColumnA = 'A' AND ColumnB to 'Z', IN THIS ALSO CASE ColumnC should NOT be updated to 'C'

--

UPDATE YourTableNmT 
SET ColumnA = 'A' , ColumnB = 'Z'
WHERE Id = 3

SELECT * FROM YourTableNmT

--
[code]

OUTPUT

[code]

/*------------------------
SELECT * FROM YourTableNmT
------------------------*/
Id          ColumnA ColumnB ColumnC
----------- ------- ------- -------
1           A       B       C
2           A       s       v
3           A       Z       w
4           w       q       m
5           t       p       n
5           A       B       o

(6 row(s) affected)

Select allOpen in new window

CREATE TRIGGER table_name_trg_update
ON dbo.table_name
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(ColumnA) OR UPDATE(ColumnB) /*either ColumnA or ColumnB referenced in UPDATE SET clause*/
--IF UPDATE(ColumnA) AND UPDATE(ColumnB)  /*use if BOTH ColumnA or ColumnB must be referenced in SET clause*/
BEGIN
    UPDATE tn
    SET ColumnC = 'C'
    FROM dbo.table_name
    INNER JOIN inserted i ON i.key_col = tn.key_col
    INNER JOIN deleted d ON d.key_col = i.key_col
    WHERE i.ColumnA = 'A' AND i.ColumnB = 'B' AND
        ((d.ColumnA IS NULL OR d.ColumnA <> 'A') OR /*verify that either ColumnA or ColumnB actually changed value*/
         (d.ColumnB IS NULL OR d.ColumnB <> 'B'))
END /*IF*/
GO


If both ColA and ColB must have changed values then, of course:
        (d.ColumnA IS NULL OR d.ColumnA <> 'A') AND
        (d.ColumnB IS NULL OR d.ColumnB <> 'B')

Very interesting problem, with a couple of built-in "gotchas"

What happens if you were to insert ColumnA = 'A' and ColumnB = 'B'
What happens if you wanted to manually override ColumnC

I would suggest that a computed column might be a better choice in these types of circumstances (more later)

Anyway, let's assume we need to include an INSERT as well as UPDATE

CREATE TRIGGER trg_YourTableName on YourTableName
AFTER INSERT,UPDATE   
AS   
    SET NOCOUNT ON;
    -- IF NOT UPDATE(ColumnC)
       UPDATE YourTableName
	   SET ColumnC = 'C' 
	   FROM YourTableName Y 
	   INNER JOIN inserted i ON Y.Id = i.Id    
       WHERE I.ColumnA = 'A' AND I.ColumnB = 'B'

GO 

Select allOpen in new window

You will note that I have a line commented out  "-- IF NOT UPDATE(ColumnC)"
For an INSERT, ColumnC is automatically included in the row being inserted, so by commenting it out, when I do and insert with ColumnA = 'A' and ColumnsB = 'B', then ColumnC would have a 'C'. However in commenting it out, I can not then specifically override the value 'C' if by chance an 'A' + 'B' combination became a different value whereby 'C' might prove to be erroneous.

But if I uncomment that line, then the insert of ColumnA = 'A' and ColumnB = 'B' wont generate ColumnC = 'C' (a change would).

So, have to do more work....

The reality is, I should only be checking the conditions whereby ColumnC might become a 'C'

ALTER TRIGGER trg_YourTableName on YourTableName
AFTER INSERT,UPDATE   
AS   
    SET NOCOUNT ON;
    IF (UPDATE(ColumnA) or UPDATE(ColumnB)) 
       UPDATE YourTableName
	   SET ColumnC = 'C' 
	   FROM YourTableName Y 
	   INNER JOIN inserted i ON Y.Id = i.Id    
       WHERE I.ColumnA = 'A' AND I.ColumnB = 'B'

GO 

Select allOpen in new window

But there are still possible unaccounted challenges (maybe not in this example, but triggers can become complex), So, in this particular example, it is sometimes better to use a computed / calculated column to generate the 'C' value. So, in the example below, I have included a new ColumnD.

CREATE TABLE YourTableName
(
     Id INT  primary key
    ,ColumnA CHAR(1)
    ,ColumnB CHAR(1)
    ,ColumnC CHAR(1)
    ,ColumnD AS CASE when ColumnA = 'A' and ColumnB = 'B' then 'C' else ' ' END
)
GO

Select allOpen in new window

Have a play and see what you think... Be very interested in hearing your feedback.

Oh, and in the examples, we have all used INSERTED and/or DELETED which are (temporary style) of Tables automatically generated by SQL Server.

THE ABOVE WILL NOT WORK since we are not updating user's table,  Also there is no where clause etc..

@Pawan, this comment is related to my example?
There is a WHERE clause and if you really know triggers, changing the inserted table it will affect the updated rows and columns of the respective table.
Also, what is "etc" in this case? Can you give more examples of what's wrong?