SEGUNDA FASE / S2B MIC PERNAMBUCO

1. SEGUNDA FASE / S2BMIC PERNAMBUCO Banco de Dados Turma: Manhã / FIR Recife-PE www.micpernambuco.com.br

2. FUNÇÕES • Criando uma função definida pelo usuário • Definindo permissões para funções definidas pelo usuário • Alterando e descartando funções definidas pelo usuário

3. FUNÇÕES Função escalar: USE Supermercado GO CREATE FUNCTION fn_VerificaValor (@valorentrada nvarchar(30)) RETURNS nvarchar(30) BEGIN IF @valorentrada IS NULL SET @valorentrada = ‘Valor nao aplicável.' RETURN @valorentrada END SELECT idProduto, dbo.fn_VerificaValor(QuantidadeVendida) FROM Vendas

4. FUNÇÕES • Alterando funções • Mantém permissões atribuídas • Faz com que a nova definição de função substitua a definição existente • Descartando funções ALTER FUNCTION dbo.fn_VerificaValor <Novo conteúdo de função> DROP FUNCTION dbo.fn_VerificaValor

5. FUNÇÕES • A cláusula RETURNS especifica o tipo de dados • A função é definida em um bloco BEGIN...END • O tipo de retorno poderá ser qualquer tipo de dado, exceto text, ntext, image, cursor ou timestamp

6. FUNÇÕES Função in-line: USE Banco GO CREATE FUNCTION fn_ExibeEstado ( @Nome nvarchar(30) ) RETURNS table AS RETURN ( SELECT ID_Estado, NomeEstado, DescricaoEstado FROM Banco.dbo.Estados WHERE NomeEstado = @Nome ) SELECT * FROM fn_ExibeEstado(‘Pernambuco’)

7. TRIGGERS (DISPARADORES) • Associados a uma tabela • Chamados automaticamente • Não podem ser chamados diretamente • É parte de uma transação

8. TRIGGERS (DISPARADORES) • Requer permissões apropriadas • Não pode conter certas instruções Use [Banco de uma empresa] GO CREATE TRIGGER AoApagarFuncionario ON Funcionarios FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN RAISERROR( ‘Você não pode apagar mais de um funcionário ao mesmo tempo.', 16, 1) ROLLBACK TRANSACTION END

9. TRIGGERS (DISPARADORES) • Alterando um disparador • Altera a definição sem descartar o disparador USE Northwind GO ALTER TRIGGER Empl_Delete ON Employees FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 6 BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION END

10. Como funciona um disparador INSERT Como funciona um disparador DELETE Como funciona um disparador UPDATE TRIGGERS (DISPARADORES)

11. As ações de TRIGGER são executadas Trigger Code: USE Northwind CREATE TRIGGER OrdDet_Insert ON [Order Details] FOR INSERT AS UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID Instrução INSERT para uma tabela com umdisparador INSERT definido Instrução INSERT registrada Ações de disparador executadas 1 2 3 Order Details 10523 2 19.00 5 0.2 Produtos OrderID ProductID UnitPrice Quantity Discount ProductID UnitsInStock … … 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0 1 2 3 4 15 106520 Instrução INSERT registrada 2 15 inserted 10523 2 19.00 5 0.2 10523 2 19.00 5 0.2 TRIGGERS (DISPARADORES) Instrução INSERT para uma tabela com um disparador INSERT definido INSERT [Order Details] VALUES (10525, 2, 19.00, 5, 0.2) Order Details OrderID ProductID UnitPrice Quantity Discount UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0

12. Ações de disparador executadas Products Instrução DELETE para uma tabela com um disparador DELETE definido Instrução DELETE registrada Ações de disparador executadas 1 ProductID Discontinued … … 1 2 3 4 0 000 2 2 1 USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID 4 Dairy Products Cheeses 0x15… 3 Instrução DELETE registrada Deleted 4 Dairy Products Cheeses 0x15… TRIGGERS (DISPARADORES) Instrução DELETE para uma tabela com um disparadorDELETE definido Categories CategoryID CategoryName Description Picture DELETE Categories WHERE CategoryID = 4 1 2 3 Beverages Condiments Confections Soft drinks, coffees… Sweet and savory … Desserts, candies, … 0x15…0x15… 0x15… UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID

13. As ações de TRIGGER são executadas Instrução UPDATE para uma tabela com um disparador UPDATE definido USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION UPDATE Employees SET EmployeeID = 17 WHERE EmployeeID = 2 Instrução UPDATE para uma tabela com um disparador UPDATE definido Instrução UPDATE registrada com instruções INSERT e DELETE Ações de disparador executadas 1 Employees AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION EmployeeID LastName FirstName Title HireDate 2 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ 2 Fuller Andrew Vice Pres. ~~~ Transaction cannot be processed. ***** Member number cannot be modified 3 Employees Instrução UPDATE registrada como INSERT e instruções DELETE EmployeeID LastName FirstName Title HireDate inserted 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ 17 Fuller Andrew Vice Pres. ~~~ 2 Fuller Andrew Vice Pres. ~~~ deleted 2 Fuller Andrew Vice Pres. ~~~ TRIGGERS (DISPARADORES)

14. Impondo a integridade dos dados TRIGGERS (DISPARADORES) CREATE TRIGGER BackOrderList_DeleteON Products FOR UPDATEASIF (SELECT BO.ProductID FROM BackOrdersAS BO JOIN Inserted AS I ON BO.ProductID = I.Product_ID) > 0BEGINDELETE BO FROM BackOrders AS BOINNER JOIN Inserted AS ION BO.ProductID = I.ProductIDEND Products BackOrders ProductID UnitsInStock … … ProductID UnitsOnOrder … 1 3 4 15 106520 1 12 3 15 1065 Atualizado 2 15 O disparadorexclui o registro 2 15

15. Considerações sobre o desempenho Os disparadores funcionam com rapidez, pois as tabelas Inserted e Deleted encontram-se no cache O tempo de execução é determinado pelo: Número de tabelas referenciadas Número de registros afetados As ações contidas nos disparadores consistem em uma parte implícita de uma transação TRIGGERS (DISPARADORES)