Triggers e Procedures em SQL Server – Nível Básico

---

764 visualizações

Depois um longo tempo sem contribuir, volto com mais um tema relacionado ao SQL Server.
Antes de prosseguirmos é bom alinhamos alguns conhecimentos, por exemplo: o que são triggers?

Basicamente são blocos de comandos SQL armazenados no banco de dados que ficam associados a uma tabela e que ocorrem de acordo com uma ação em uma tabela, ou seja, são executadas automaticamente de acordo com uma ação (um insert, update e ou delete).

Nota: Você não chama/executa uma trigger.

E procedures?

Procedures são blocos de comandos SQL armazenados, é como um método que tem parâmetros e pode ou não ter retorno.

Nota: Sim. Você quem chama a procedure que esta armazenada no banco de dados.

Cabe ainda alinharmos dois pontos de conhecimento: Transações e o mecanismo particular do SQL Server “INSERTED e DELETED”.
Transações servem para manter a integridade dos dados. Digamos que você tenha que atualizar duas ou mais tabelas dando comandos de insert, update e delete e tudo deve funcionar de forma única, porém digamos que falhe por algum motivo, um insert ou qualquer outro comando (porém alguns comandos anteriores funcionaram) é ai que você teria que desfazer todo o processo. Neste contexto que as transações são úteis, você cria uma transação e se algo der errado é possível desfazer caso contrario é só confirmar.
Comando para abrir uma transação BEGIN TRANSACTION, para confirmar uma transação COMMIT e desfazer ROLLBACK.
No SQL Server, há um mecanismo que funciona como uma transação quando você está inserindo dados em uma tabela o SQL Server insere uma réplica em uma tabela de sistema que se chama INSERTED, se tudo estiver ok, ele retira o registro inserido da INSERTED e mantem apenas na tabela destino. Semelhantemente ocorre o mesmo com a deleção em que o registro é armazenado em outra tabela DELETED. E em um update o registro é mantido na INSERTED (registro com alteração) e DELETED (registro antigo).
Agora que demos uma breve introdução aos mecanismos e funcionamentos, vamos montar uma série de testes e um pequeno cenário para exemplificarmos este assunto.
Vamos criar uma tabela de usuários e de log usuários (no log iremos guardar a data e ação que causou o insert na tabela(se foi um insert, update ou delete)).
Execute o script abaixo:

IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[USERS]')
AND type in (N'U'))
DROP TABLE USERS
GO
CREATE TABLE USERS
(
ID INT IDENTITY,
NAME VARCHAR(100),
BIRTHDAY DATE
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERS_LOG]') AND type in (N'U'))
DROP TABLE USERS_LOG
GO
CREATE TABLE USERS_LOG
(
OPERATION_TYPE VARCHAR(1) NOT NULL, -- U(UPDATE) I(INSERT) D(DELETE)
OPERATION_DATE DATETIME NOT NULL,
ID INT NOT NULL,
NAME VARCHAR(100),
BIRTHDAY DATE
)

Criando a trigger de insert:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE INSERÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_INS] ON [DBO].[USERS]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = I.ID,
@NAME     = I.NAME,
@BIRTHDAY = I.BIRTHDAY
FROM INSERTED I

PRINT '*INSERT* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
VALUES ('I', GETDATE(), @ID, @NAME, @BIRTHDAY)

SET NOCOUNT OFF;
END
GO

Criando a trigger de update:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE ALTERAÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_UPD] ON [DBO].[USERS]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = I.ID,
@NAME     = I.NAME,
@BIRTHDAY = I.BIRTHDAY
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID

PRINT '*UPDATE* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
VALUES ('U', GETDATE(), @ID, @NAME, @BIRTHDAY)

SET NOCOUNT OFF;
END
GO

Criando a trigger de delete:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE DELEÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_DEL] ON [DBO].[USERS]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = D.ID,
@NAME     = D.NAME,
@BIRTHDAY = D.BIRTHDAY
FROM DELETED D

PRINT '*DELETE* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
VALUES ('D', GETDATE(), @ID, @NAME, @BIRTHDAY)

SET NOCOUNT OFF;
END
GO

Comandos para testar:

SELECT * FROM USERS
SELECT * FROM USERS_LOG

--INSERIR O USUÁRIO
INSERT INTO USERS (NAME, BIRTHDAY)
VALUES('SUZUKI', DATEADD(YEAR, -10, GETDATE()))

--ALTERAR USUÁRIO
UPDATE USERS
SET NAME = 'THIAGO'
WHERE ID = 1

--DELETAR USUÁRIO
DELETE USERS
WHERE ID = 1

Ao invés de escrever os inserts que são basicamente iguais mudando apenas alguma coluna, podemos escrever uma procedure para fazer este trabalho:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		THIAGO S. SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	INSERE DADOS EM TABELA DE LOG
-- =============================================
DROP PROCEDURE [dbo].[SP_INSERE_USERSLOG]
GO
CREATE PROCEDURE [dbo].[SP_INSERE_USERSLOG]
@OPERATION_TYPE VARCHAR(1),
@ID             INT,
@NAME           VARCHAR(100),
@BIRTHDAY       DATE
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
VALUES (@OPERATION_TYPE, GETDATE(), @ID, @NAME, @BIRTHDAY)

SET NOCOUNT OFF;
END
GO

Com isso as triggers ficariam assim.
Trigger de insert:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE INSERÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_INS] ON [DBO].[USERS]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = I.ID,
@NAME     = I.NAME,
@BIRTHDAY = I.BIRTHDAY
FROM INSERTED I

PRINT '*INSERT* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

--INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
--VALUES ('I', GETDATE(), @ID, @NAME, @BIRTHDAY)

EXEC SP_INSERE_USERSLOG 'I', @ID, @NAME, @BIRTHDAY

SET NOCOUNT OFF;
END
GO

Trigger de update:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE ALTERAÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_UPD] ON [DBO].[USERS]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = I.ID,
@NAME     = I.NAME,
@BIRTHDAY = I.BIRTHDAY
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID

PRINT '*UPDATE* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
VALUES ('U', GETDATE(), @ID, @NAME, @BIRTHDAY)

--EXEC SP_INSERE_USERSLOG 'U', @ID, @NAME, @BIRTHDAY

SET NOCOUNT OFF;
END
GO

Trigger de Delete:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		SUZUKI
-- CREATE DATE: 29/07/2010
-- DESCRIPTION:	TRIGGER QUE GRAVA LOG DE DELEÇÃO
-- =============================================
CREATE TRIGGER [DBO].[TRG_USERS_DEL] ON [DBO].[USERS]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ID       INT,
@NAME     VARCHAR(100),
@BIRTHDAY DATE

SELECT @ID       = D.ID,
@NAME     = D.NAME,
@BIRTHDAY = D.BIRTHDAY
FROM DELETED D

PRINT '*DELETE* ' +
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
', @NAME: '     + @NAME +
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))

--INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
--VALUES ('D', GETDATE(), @ID, @NAME, @BIRTHDAY)

EXEC SP_INSERE_USERSLOG 'D', @ID, @NAME, @BIRTHDAY

SET NOCOUNT OFF;
END
GO

Com isso, vimos basicamente como fazer um pequeno exemplo de log com trigger de insert, update, delete e um procedure.
Nota: poderia ser feita apenas uma trigger que fosse disparada (no insert, update e dele), porém para efeitos didáticos fizemos separadamente. A procedure faz um insert e recebe parâmetros, porém poderia fazer um select em uma tabela e não ter parâmetros.
Espero que este pequeno texto tenha ajudado você a ganhar algum conhecimento adicional.
Fiz os testes com SQL Server Express 2008.
Sayonara e até a próxima.

Tenha em mente que tudo que você aprende na escola é trabalho de muitas gerações. Receba essa herança, honre-a, acrescente a ela e, um dia, fielmente, deposite-a nas mãos de seus filhos. – “Albert Einstein”

Posts relacionados:

1. Triggers e Procedures em SQL Server – Nível Intermediário
2. Procedure de Backup no SQL Server
3. Funções Úteis SQL-Server
4. View (SQL, select, insert, update e delete)
5. REBUILD EM BANCOS SQL SERVER

-->