quarta-feira, 20 de agosto de 2014

Tips for toggle, or flip, bit columns

Some tips I record here just to get them in touch.

Looking for a toggle function in T-SQL, I've found these tips in StackOverflow:

update table_name set status = status ^ 1  where code = 123 [Using XOR]
update table_name set status = 1 - status  where code = 123 [Using minus logically]

In my case, I had a nullable field, with many nulls inside, so I had to adapt:
update table_name set status = isnull(status,0) ^ 1  where code = 123
update table_name set status = 1 - isnull(status,0)  where code = 123

I have never needed to do this directly to the database, but only in high level tiers.

Usually I would do it in a more readable way, like in Mayo contribution:
UPDATE tblTest SET MyBitField = CASE WHEN MyBitField = 1 THEN 0 ELSE 1 END


   MyBitField = CASE 
      WHEN MyBitField = 1 THEN 0 
      WHEN MyBitField = 0 THEN 1
      ELSE NULL -- or 1 or 0 depending on requirements

Thanks to gbn, Mayo and Austin Salonen. You did great.

Source: http://stackoverflow.com/questions/1397332/how-to-flip-bit-fields-in-t-sql

terça-feira, 19 de agosto de 2014

About triggers, after and instead of

The following picture shows an overview of data flow when using triggers in SQL Server. More details and more arrows flows were not shown to be a simple diagram. Some anottations about:

  • Truncate table statement does not fire triggers
  • WriteText does not fire triggers, with or without logging
  • Old versions of SQL need set the DISALLOW RESULTS FROM TRIGGERS to 1, due to avoiding returning results when running a trigger
  • Use sp_settriggerorder to order the first and last AFTER trigger when using many of them in the same object. Other triggers between the last and first will run randomly
  • SERVER scoped DDL triggers are in \Server Objects\Triggers folder
  • DATABASE scoped DDL triggers are in \Programmability\Database Triggers folder
  • LOGON trigger run only when authentication is successfull
  • Inside an Instead Of trigger is not necessary to rollback a command, because its underlying commando (the original one) is ignored. If needed execute the original statement inside the trigger. The statement won't call the instead off trigger again (recursively), but the After triggers will run, whether exists.
  • Code triggers carefully because they can call each other recursively when changing other tables than the underlying table (where the trigger is attached).