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

---

3.034 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:

 SQL | 
 Copiar código |
?
 
01
IF  EXISTS (SELECT * FROM sys.objects
02
WHERE object_id = OBJECT_ID(N'[dbo].[USERS]')
03
AND TYPE IN (N'U'))
04
DROP TABLE USERS
05
GO
06
CREATE TABLE USERS
07
(
08
 
09
ID INT IDENTITY,
10
NAME VARCHAR(100),
11
BIRTHDAY DATE
12
)
13
GO
14
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERS_LOG]') AND TYPE IN (N'U'))
15
DROP TABLE USERS_LOG
16
GO
17
CREATE TABLE USERS_LOG
18
(
19
OPERATION_TYPE VARCHAR(1) NOT NULL, -- U(UPDATE) I(INSERT) D(DELETE)
20
OPERATION_DATE DATETIME NOT NULL,
21
ID INT NOT NULL,
22
NAME VARCHAR(100),
23
BIRTHDAY DATE
24
)

Criando a trigger de insert:

 SQL | 
 Copiar código |
?
 
01
SET ANSI_NULLS ON
02
 
03
GO
04
 
05
SET QUOTED_IDENTIFIER ON
06
 
07
GO
08
 
09
-- =============================================
10
 
11
-- AUTHOR: SUZUKI
12
 
13
-- CREATE DATE: 29/07/2010
14
 
15
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE INSERÇÃO
16
 
17
-- =============================================
18
 
19
CREATE TRIGGER [DBO].[TRG_USERS_INS] ON [DBO].[USERS]
20
 
21
AFTER INSERT
22
 
23
AS
24
 
25
BEGIN
26
 
27
SET NOCOUNT ON;
28
 
29
DECLARE @ID       INT,
30
 
31
@NAME     VARCHAR(100),
32
 
33
@BIRTHDAY DATE
34
 
35
SELECT @ID       = I.ID,
36
 
37
@NAME     = I.NAME,
38
 
39
@BIRTHDAY = I.BIRTHDAY
40
 
41
FROM INSERTED I
42
 
43
PRINT '*INSERT* ' +
44
 
45
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
46
 
47
', @NAME: '     + @NAME +
48
 
49
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
50
 
51
INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
52
 
53
VALUES ('I', GETDATE(), @ID, @NAME, @BIRTHDAY)
54
 
55
SET NOCOUNT OFF;
56
 
57
END
58
 
59
GO
60

Criando a trigger de update:

 SQL | 
 Copiar código |
?
 
01
02
 
03
SET ANSI_NULLS ON
04
 
05
GO
06
 
07
SET QUOTED_IDENTIFIER ON
08
 
09
GO
10
 
11
-- =============================================
12
 
13
-- AUTHOR: SUZUKI
14
 
15
-- CREATE DATE: 29/07/2010
16
 
17
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE ALTERAÇÃO
18
 
19
-- =============================================
20
 
21
CREATE TRIGGER [DBO].[TRG_USERS_UPD] ON [DBO].[USERS]
22
 
23
AFTER UPDATE
24
 
25
AS
26
 
27
BEGIN
28
 
29
SET NOCOUNT ON;
30
 
31
DECLARE @ID       INT,
32
 
33
@NAME     VARCHAR(100),
34
 
35
@BIRTHDAY DATE
36
 
37
SELECT @ID       = I.ID,
38
 
39
@NAME     = I.NAME,
40
 
41
@BIRTHDAY = I.BIRTHDAY
42
 
43
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
44
 
45
PRINT '*UPDATE* ' +
46
 
47
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
48
 
49
', @NAME: '     + @NAME +
50
 
51
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
52
 
53
INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
54
 
55
VALUES ('U', GETDATE(), @ID, @NAME, @BIRTHDAY)
56
 
57
SET NOCOUNT OFF;
58
 
59
END
60
 
61
GO
62

Criando a trigger de delete:

 SQL | 
 Copiar código |
?
 
01
02
SET ANSI_NULLS ON
03
 
04
GO
05
 
06
SET QUOTED_IDENTIFIER ON
07
 
08
GO
09
 
10
-- =============================================
11
 
12
-- AUTHOR: SUZUKI
13
 
14
-- CREATE DATE: 29/07/2010
15
 
16
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE DELEÇÃO
17
 
18
-- =============================================
19
 
20
CREATE TRIGGER [DBO].[TRG_USERS_DEL] ON [DBO].[USERS]
21
 
22
AFTER DELETE
23
 
24
AS
25
 
26
BEGIN
27
 
28
SET NOCOUNT ON;
29
 
30
DECLARE @ID       INT,
31
 
32
@NAME     VARCHAR(100),
33
 
34
@BIRTHDAY DATE
35
 
36
SELECT @ID       = D.ID,
37
 
38
@NAME     = D.NAME,
39
 
40
@BIRTHDAY = D.BIRTHDAY
41
 
42
FROM DELETED D
43
 
44
PRINT '*DELETE* ' +
45
 
46
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
47
 
48
', @NAME: '     + @NAME +
49
 
50
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
51
 
52
INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
53
 
54
VALUES ('D', GETDATE(), @ID, @NAME, @BIRTHDAY)
55
 
56
SET NOCOUNT OFF;
57
 
58
END
59
 
60
GO

Comandos para testar:

 SQL | 
 Copiar código |
?
 
01
02
SELECT * FROM USERS
03
 
04
SELECT * FROM USERS_LOG
05
 
06
--INSERIR O USUÁRIO
07
 
08
INSERT INTO USERS (NAME, BIRTHDAY)
09
 
10
VALUES('SUZUKI', DATEADD(YEAR, -10, GETDATE()))
11
 
12
--ALTERAR USUÁRIO
13
 
14
UPDATE USERS
15
 
16
SET NAME = 'THIAGO'
17
 
18
WHERE ID = 1
19
 
20
--DELETAR USUÁRIO
21
 
22
DELETE USERS
23
 
24
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:

 SQL | 
 Copiar código |
?
 
01
SET ANSI_NULLS ON
02
 
03
GO
04
 
05
SET QUOTED_IDENTIFIER ON
06
 
07
GO
08
 
09
-- =============================================
10
 
11
-- AUTHOR: THIAGO S. SUZUKI
12
 
13
-- CREATE DATE: 29/07/2010
14
 
15
-- DESCRIPTION: INSERE DADOS EM TABELA DE LOG
16
 
17
-- =============================================
18
 
19
DROP PROCEDURE [dbo].[SP_INSERE_USERSLOG]
20
 
21
GO
22
 
23
CREATE PROCEDURE [dbo].[SP_INSERE_USERSLOG]
24
 
25
@OPERATION_TYPE VARCHAR(1),
26
 
27
@ID             INT,
28
 
29
@NAME           VARCHAR(100),
30
 
31
@BIRTHDAY       DATE
32
 
33
AS
34
 
35
BEGIN
36
 
37
SET NOCOUNT ON;
38
 
39
INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
40
 
41
VALUES (@OPERATION_TYPE, GETDATE(), @ID, @NAME, @BIRTHDAY)
42
 
43
SET NOCOUNT OFF;
44
 
45
END
46
 
47
GO

Com isso as triggers ficariam assim.

Trigger de insert:

 SQL | 
 Copiar código |
?
 
01
SET ANSI_NULLS ON
02
 
03
GO
04
 
05
SET QUOTED_IDENTIFIER ON
06
 
07
GO
08
 
09
-- =============================================
10
 
11
-- AUTHOR: SUZUKI
12
 
13
-- CREATE DATE: 29/07/2010
14
 
15
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE INSERÇÃO
16
 
17
-- =============================================
18
 
19
CREATE TRIGGER [DBO].[TRG_USERS_INS] ON [DBO].[USERS]
20
 
21
AFTER INSERT
22
 
23
AS
24
 
25
BEGIN
26
 
27
SET NOCOUNT ON;
28
 
29
DECLARE @ID       INT,
30
 
31
@NAME     VARCHAR(100),
32
 
33
@BIRTHDAY DATE
34
 
35
SELECT @ID       = I.ID,
36
 
37
@NAME     = I.NAME,
38
 
39
@BIRTHDAY = I.BIRTHDAY
40
 
41
FROM INSERTED I
42
 
43
PRINT '*INSERT* ' +
44
 
45
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
46
 
47
', @NAME: '     + @NAME +
48
 
49
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
50
 
51
--INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
52
 
53
--VALUES ('I', GETDATE(), @ID, @NAME, @BIRTHDAY)
54
 
55
EXEC SP_INSERE_USERSLOG 'I', @ID, @NAME, @BIRTHDAY
56
 
57
SET NOCOUNT OFF;
58
 
59
END
60
 
61
GO

Trigger de update:

 SQL | 
 Copiar código |
?
 
01
02
SET ANSI_NULLS ON
03
 
04
GO
05
 
06
SET QUOTED_IDENTIFIER ON
07
 
08
GO
09
 
10
-- =============================================
11
 
12
-- AUTHOR: SUZUKI
13
 
14
-- CREATE DATE: 29/07/2010
15
 
16
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE ALTERAÇÃO
17
 
18
-- =============================================
19
 
20
CREATE TRIGGER [DBO].[TRG_USERS_UPD] ON [DBO].[USERS]
21
 
22
AFTER UPDATE
23
 
24
AS
25
 
26
BEGIN
27
 
28
SET NOCOUNT ON;
29
 
30
DECLARE @ID       INT,
31
 
32
@NAME     VARCHAR(100),
33
 
34
@BIRTHDAY DATE
35
 
36
SELECT @ID       = I.ID,
37
 
38
@NAME     = I.NAME,
39
 
40
@BIRTHDAY = I.BIRTHDAY
41
 
42
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
43
 
44
PRINT '*UPDATE* ' +
45
 
46
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
47
 
48
', @NAME: '     + @NAME +
49
 
50
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
51
 
52
INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
53
 
54
VALUES ('U', GETDATE(), @ID, @NAME, @BIRTHDAY)
55
 
56
--EXEC SP_INSERE_USERSLOG 'U', @ID, @NAME, @BIRTHDAY
57
 
58
SET NOCOUNT OFF;
59
 
60
END
61
 
62
GO

Trigger de Delete:

 SQL | 
 Copiar código |
?
 
01
SET ANSI_NULLS ON
02
 
03
GO
04
 
05
SET QUOTED_IDENTIFIER ON
06
 
07
GO
08
 
09
-- =============================================
10
 
11
-- AUTHOR: SUZUKI
12
 
13
-- CREATE DATE: 29/07/2010
14
 
15
-- DESCRIPTION: TRIGGER QUE GRAVA LOG DE DELEÇÃO
16
 
17
-- =============================================
18
 
19
CREATE TRIGGER [DBO].[TRG_USERS_DEL] ON [DBO].[USERS]
20
 
21
AFTER DELETE
22
 
23
AS
24
 
25
BEGIN
26
 
27
SET NOCOUNT ON;
28
 
29
DECLARE @ID       INT,
30
 
31
@NAME     VARCHAR(100),
32
 
33
@BIRTHDAY DATE
34
 
35
SELECT @ID       = D.ID,
36
 
37
@NAME     = D.NAME,
38
 
39
@BIRTHDAY = D.BIRTHDAY
40
 
41
FROM DELETED D
42
 
43
PRINT '*DELETE* ' +
44
 
45
'@ID: '         + CAST(@ID AS VARCHAR(100)) +
46
 
47
', @NAME: '     + @NAME +
48
 
49
', @BIRTHDAY: ' + CAST(@BIRTHDAY AS VARCHAR(10))
50
 
51
--INSERT INTO USERS_LOG (OPERATION_TYPE,OPERATION_DATE,ID, NAME, BIRTHDAY)
52
 
53
--VALUES ('D', GETDATE(), @ID, @NAME, @BIRTHDAY)
54
 
55
EXEC SP_INSERE_USERSLOG 'D', @ID, @NAME, @BIRTHDAY
56
 
57
SET NOCOUNT OFF;
58
 
59
END
60
 
61
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”

-->