Update inóquo também aciona trigger

Ao executar um update em que a condição não seja atendida, 0 linhas serão afetadas (inóquo, i.e., sem efeito), e os triggers associados serão executados, mesmo que nenhum dado seja modificado. O único problema aqui é executar alguma ação que conte com o acionamento do trigger sem testar se realmente algum dado foi modificado.

Acabei de eliminar um bug que estava enviando email sobre a exclusão de consumidor porque em qualquer condição que não era uma inclusão ele entrava no ELSE e montava o corpo do email. O bug é que em caso de alteração ou de falha de update o email era enviado, e nestes casos, vazios.

Exemplo:
if exists(select * from inserted)
{código do insert/update}
else
{código do update/delete e dos casos acima, que pode ser um bug ou algo especial}

Uma possível solução:
if exists(select * from inserted)
{código do insert/update}
else if exists(select * from deleted)
{código do update/delete e dos casos acima, que pode ser um bug ou algo especial}

Um código independente pode estar fora do IF..ELSE.

That's all for now, folks!

Enabling public role database for use (at your own risk)

Why a recently created login cannot connect to a database if every login/user is by default associated to the public role? Because that role is not configured to gain automatic access to the database!
That's how you enable it: On the Database which you can grant public access right click it and choose permissions. Search for the public database role and add it to the list of users. Grant Connect and Select to it on the list of permissions. Click OK.
Every new login created will gain access automatically for connect and select every securable object of that database. Additional permissions must be granted for each user according to his/her security rights. Do this for each database you want to enable to use the public database role.
Advice: Do not do that if you have any doubt about the risks involved. Don't grant any other privileges than connect and select. It's meant to test and learning only. As someone said before, database it's not child's play.

SQL Backup types: Full and Differential, not Incremental

When using SQL Server backup remember it does not use the incremental mode, so, when recovering something like following picture (Full + many differentials), select the FULL plus the ONE differential you want, not more than one. It`s not going to cause an error, but its effect is the same of selecting the older one from your list. Generally, for the most up to date backup just select the full plus the most recent differential.








 If you select just the differential the message below (pt-br) will be presented










The incremental mode is not supported in 2008 R2 (at least), which is used to save every changes from the last incremental backup. Differential is always referencing the full backup, while incremental references the last incremental.
More about backup types is very easy to find in Internet. Good searchs.