T-SQL: CROSS APPLY

Prosseguindo na série com as instruções novas no SQL Server 2005, vou mostrar neste post o CROSS APPLY. Em uma operação de JOIN (INNER, OUTER ou CROSS) o T-SQL não aceita subquery correlacionada em tabela derivada, veja os exemplos abaixo:

select A.col, b.col
from A
cross join (select B.col from B where B.val=A.val) b
-- Erro, A.val na subquery está fora de escopo!

A instrução acima deverá ser reescrita em uma das forma abaixo, utilizando subquery como tabela derivada:

select A.col, b.col
from A
cross join (select B.col,B.val from B) b
where B.val=A.val
OU
select A.col, b.col
from A join (select B.col,B.val from B) b
on B.val=A.val

O problema persiste quando tentamos utilizar uma Função Definida pelo Usuário (UDF), porque A.val como parâmetro da função está fora de escopo!

select A.*, B.col from A
cross join dbo.UDF(A.val) B

O novo CROSS APPLY do SQL Server 2005 resolve este problema, aceitando a instrução abaixo:

select A.*, B.col from A
CROSS APPLY dbo.UDF(A.val) B

Vamos criar duas tabelas e uma função para trabalharmos em um exemplo real:

use TempDB
go
create table Cliente(ClientePK int NULL,Nome varchar(30) NULL)
create table Vendas(VendasPK int NULL,ClienteFK int NULL,Valor decimal(9,2) NULL)
go
insert Cliente values (1,'Jose')
insert Cliente values (2,'Maria')
insert Cliente values (3,'Ana')

insert Vendas values (1,1,20.00)
insert Vendas values (2,1,40.00)
insert Vendas values (3,2,15.00)
insert Vendas values (3,2,36.00)
go

create function fnu_MaiorVenda(@ClienteID int)
returns table as return(
select ClienteFK,Max(Valor) MaiorValor from Vendas
where ClienteFK = @ClienteID group by ClienteFK)
go

A função fnu_MaiorVenda faz um GROUP BY na tabela Vendas, retornando o valor da maior venda de um cliente identificado pelo parâmetro de entrada @ClienteID. Ao tentar executar um JOIN entre a tabela Cliente e a função fnu_MaiorVenda, recebemos o erro 4104:

select c.Nome, v.MaiorValor
from Cliente c JOIN fnu_MaiorVenda(c.ClientePK) v
on c.ClientePK = v.ClienteFK
-- Msg 4104, Level 16, State 1, Line 1
-- The multi-part identifier "c.ClientePK" could not be bound.

O CROSS APPLY resolve o problema...

select c.Nome, v.MaiorValor
from Cliente c CROSS APPLY fnu_MaiorVenda(c.ClientePK) v

Até o próximo post.
Landry.

Trigger de Login

No último curso de Design de Segurança do SQL Server 2005 (curso 2790), um aluno com experiência em Oracle, perguntou se o SQL Server tinha Trigger de Login. Respondi que não tinha, porém poderíamos simular esta funcionalidade com Event Notification... o problema é que ele bloqueava o login em algumas situações, dependendo do usuário e da aplicação utilizada no login, o que seria impossível já que Event Notification é assíncrono! Uma rápida consulta na Internet mostrou uma nova funcionalidade incluída no Service Pack 2 do SQL Server 2005: Trigger de Login!

A Trigger de Login foi incluída para atender a uma certificação de segurança chamada Common Criteria (CC), resultante da união de três outras certificações: ITSEC (padrão Europeu), CTCPEC (padrão Canadense) e TCSEC (Departamento de Defesa Norte Americano). A certificação CC é reconhecida por mais de 24 países e possui 7 níveis para produtos de informática, de EAL1 a EAL7.

O SQL Server até SP1 foi classificado em EAL1, já com SP2 recebeu a classificação EAL4+ (o + indica atendimento parcial, já que a Microsoft irá melhorar o suporte em atualizações futuras). A Trigger de Login foi criada para atender os seguintes requisitos que constam no EAL4+:
Restringir a quantidade máxima de conexões concorrentes de um mesmo usuário.
Definir uma quantidade máxima default de conexões por usuário.
Negar a conexão com base no usuário, grupo, dia da semana, etc.

Dentro de uma Trigger de Login você pode utilizar a função EVENTDATA() para obter informações da conexão que originou o disparo da trigger, veja o documento XML gerado abaixo:



Outras fontes de informações que podem ser utilizadas dentro da trigger:
- sys.dm_exec_sessions – View dinâmica com informações das sessões abertas.
- sys.dm_exec_connections – View dinâmica com informações das conexões abertas.
- app_name() – nome da aplicação utilizada para realizar a conexão corrente
- CURRENT_USER – usuário de banco de dados da conexão corrente

Exemplo:
-- Retorna Informações da conexão corrente
select s.*,c.*
from sys.dm_exec_sessions s
join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid

O exemplo abaixo cria uma Trigger de Login que restringe o acesso ao servidor a partir do Management Studio apenas para o Administrador, além de registrar em uma tabela de auditoria os logins com sucesso.

-- Tabela de Auditoria na MSDB
create table msdb.dbo.AutitLogin (
idPK int not null identity,
Data datetime null,
ProcID int null,
LoginID varchar(128) null,
NomeHost varchar(128) null,
App varchar(128) null,
SchemaAutenticacao varchar(128) null,
Protocolo varchar(128) null,
IPcliente varchar(30) null,
IPservidor varchar(30) null,
xmlConectInfo xml)
go

-- Trigger de Login
create trigger AuditLogin on all server
for logon
as
IF CURRENT_USER <> 'dbo' and
app_name() like 'Microsoft SQL Server Management Studio%'
rollback
else
-- Login sucesso
insert msdb.dbo.AutitLogin
select getdate(),@@spid,s.login_name,s.[host_name],
s.program_name,c.auth_scheme,c.net_transport,
c.client_net_address,c.local_net_address,eventdata()
from sys.dm_exec_sessions s join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid
go

Se um usuário comum tentar abrir uma conexão no SQL Server a partir do Managemente Studio irá receber a mensagem de erro abaixo:



A mensagem de erro poderia ser melhor, sem expor o motivo da falha da conexão: “trigger execution”! Você pode votar no site Microsoft Connect para alterar a mensagem de erro no link abaixo:
https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008

Gostaria de agradecer a Diego Cerqueira (o aluno especializado em Oracle) por ter enriquecido a aula com sues questionamentos, dando origem a este post.

Até o próximo post,
Landry.

T-SQL: OUTPUT

Olá... neste post veremos outra novidade no Transact-SQL do SQL Server 2005, a cláusula OUTPUT. Esta cláusula pode ser acrescentada a uma instrução de atualização para retornar os dados que acabaram de ser atualizados. Vamos criar uma tabela no Banco de Dados TEMPDB para utilizar nos exemplos:

use tempdb
go
create table TesteOutput (ColPK int IDENTITY NOT NULL, Nome varchar(50), Tel varchar(20))

Repare que a tabela TesteOutput possui a propriedade IDENTITY (auto numeração) na primeira coluna (ColPK)! Uma necessidade comum é retornar o valor atribuído pelo SQL Server a coluna com propriedade IDENTITY durante um INSERT, sendo necessário executar um SELECT após o INSERT.

insert TesteOutput values ('Ana Lucia','1111-1111')
select SCOPE_IDENTITY()

Valor_ColPK
-------------------
1

O SQL Server 2005 pode simplificar a operação acima em um comando apenas, veja:

insert TesteOutput OUTPUT inserted.ColPK
values ('Maria Clara','2222-2222')

ColPK
-----------
2

Se você quiser pode até retornar todas as colunas da tabela:

insert TesteOutput OUTPUT inserted.*
values ('Ana Paula','3333-3333')

Veja no DELETE:

delete TesteOutput OUTPUT deleted.ColPK,deleted.Nome
where ColPK = 1

Agora um exemplo mais interessante.... você precisa manter uma tabela de auditoria registrando o usuário, operação e a data que ocorreu a atualização! No SQL Server 2000 existem duas opções: TRIGGER ou executar duas operações, veja no SQL 2005:

create table TesteHist (
Nome varchar(50),
Operacao varchar(10),
Data datetime,
Usuario varchar(256))
go

delete TesteOutput OUTPUT
deleted.Nome,'DETELE',getdate(),suser_sname()
into TesteHist
where ColPK = 2
go

select * from TesteHist



Até o próximo post.
Landry.