mysql COLUMN_UPDATED() and bitwise operations

Sometimes to achieve a certain functionality, you need to determine the columns that were updated after the mysql update query. I will try to explain how to achieve this in this particular article. The article contains the reference to cakephp constructs because i happened to use this in a cakephp application. Handling Bits in General: Let me discuss about the basics of how we can do bitwise operations in php(The code contains references to cakephp constructs but the logic remains the same).
  • Depending on the number of bits that need to be used declare an attribute of type INT.
  • Tiny – 8bits, Small- 16 bits, Med – 24  bits or Int – 32 bits
  • smallint $status = 0;
  • Declare each flag that needs to be used as following in model in powers of 2.
<code><em>class</em><em> Node </em><em>extends</em><em> AppModel {</em></code>

<em>const</em><em> flag1 =1;//00000001 </em>

<em>const</em><em> flag2 =2;//00000010</em>

<em>const</em><em> flag3 =4;//00000100</em>

<em>const</em><em> flag4 =8;//00001000</em>

  • Check if a particular flag is set:  if($status & Node::flag2) { echo “flag 2 is set”;}
  • Set a flag: $status |= Node::flag3;
  • Unset a flag: $status = $status &~ Node::flag3;
  • Check how many flags are set:
<em>function </em><em>get_count($status){</em>

<em> $i=0;</em>

<em> while ($status){ $i++ ; $status &= ($status - 1) ; }</em>

<em> return $i; </em>

<em> }
MySql – COLUMNS_UPDATED() Mysql columns_updated works on the similar logic as explained above. COLUMNS_UPDATED() returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated. COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions. COLUMNS_UPDATED returns one or more bytes that are ordered from left to right, with the least significant bit in each byte being the rightmost. The rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost. COLUMNS_UPDATED returns TRUE for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted. To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the columns being tested. For example, table t1 contains columns C1, C2, C3, C4, and C5. To verify that columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), follow the syntax with & 14(bitwise and operation). To test whether only column C2 is updated, specify & 2. Hope this helps someone !!