SQL Server Triggers, Cursors and Functions: Practical Examples

SQL Server Triggers, Cursors and Functions

Trigger for DELETE on ItensLocacao

create trigger TRG_excluirItemLocacao ON ItensLocacao for DELETE AS begin if (select COUNT(*) from deleted) > 0 begin update Midias set qtdEstoque = (qtdEstoque + 1) from Midias M inner join deleted D on D.CodMidia = M.CodMidia where M.CodMidia = D.CodMidia print 'Locação excluída com sucesso' end else begin print 'Não foi possível excluir a locação' rollback transaction end end

Trigger for INSERT on ItensLocacao

create trigger TRG_InsertItenLocacaoi ON ItensLocacao for INSERT AS begin if (select COUNT(*) FROM inserted D inner join Locacao L on L.CodLocacao = D.CodLocacao inner join Midias M on M.CodMidia = D.CodMidia where M.qtdEstoque > 0) = 1 begin update Midias set qtdEstoque = (M.qtdEstoque - 1) from Midias M inner join inserted D on D.CodMidia = M.CodMidia inner join Locacao L on D.CodLocacao = L.CodLocacao where M.CodMidia = D.CodMidia print 'O REGISTRO FOI INSERIDO COM SUCESSO' end else begin print 'MÍDIA INDISPONÍVEL NO ESTOQUE' rollback transaction end end

Disabling a trigger

alter table nome_da_tabela disable trigger nome_da_trigger

Cursor example: updating employee names

declare @func_id int declare @func_nome varchar(50) declare cursor_teste cursor for select func_id, func_nome from funcionarios open cursor_teste FETCH NEXT FROM cursor_teste INTO @func_id, @func_nome while @@fetch_Status = 0 Begin if charindex('k', @func_nome) != 0 begin set @func_nome = @func_nome + ' Souza' -- if the name contains the letter k update funcionarios set func_nome = @func_nome where current of cursor_teste end print cast(@func_id as varchar(5)) + ' ' + @func_nome FETCH NEXT FROM cursor_teste INTO @func_id, @func_nome end close cursor_teste deallocate cursor_teste

Table-valued function: employees by department

create function fnc_func_setor (@setor int) Returns @tbl_func_Setor Table (func_id int, func_name varchar(50), setor_id int, setor_nome varchar(50)) as begin insert @tbl_func_Setor select funcionarios.func_id, funcionarios.func_nome, setores.setor_id, setores.setor_nome from funcionarios inner join setores on setores.setor_id = funcionarios.setor_id where funcionarios.setor_id = @setor Return end

Calling the function

select * from dbo.fnc_RetornaTabelaFuncionarios(500, 1000)

Subquery with aggregate

select C.ContactName, (select Sum(O.Freight) from Orders O inner join Customers C on C.CustomerID = O.CustomerID group by C.CustomerID) from Customers C