--Aula 01 - Implementing Constraints
--===================================================================--Autor : Lineu Alves Lima Filho
--Data Criação : 10/03/2009
--Descrição : Módulo 04
--===================================================================
--Versão : 1.0
--===================================================================
--Versão :
--Data Atualização :
--===================================================================
USE [master]
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE [name] = 'DB6232')
DROP DATABASE DB6232
GO
CREATE DATABASE DB6232
GO
USE DB6232
GO
CREATE TABLE clientes
(
cod_cli INT IDENTITY PRIMARY KEY,
nome_cli CHAR(50) NOT NULL,
rua_cli CHAR(50),
num_cli INT CHECK (num_cli >= 0),
rg_cli CHAR(15) NOT NULL UNIQUE (rg_cli),
cpf_cli CHAR(15) NOT NULL UNIQUE (cpf_cli),
idade_cli CHAR(02) DEFAULT ('0'),
sexo_cli CHAR(01) CHECK (sexo_cli='F' OR sexo_cli='M'),
data_insc_cli DATETIME DEFAULT (GETDATE())
)
GO
sp_help 'clientes'
sp_helpconstraint 'clientes'
--Testando as colunas idade_cli e data_insc_cli com as constraints
--DEFAULT ('0') e DEFAULT (GETDATE())
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli)
VALUES ('Marina', '12.450.459-3', '234.687.576-45')
SELECT TOP 10 * FROM clientes
--Inserindo explicitamente dados na coluna idade_cli
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Joana', '23.687.354-4', '263.687.306-11', '25')
SELECT TOP 10 * FROM clientes
--Testando a UNIQUE RG
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Julia', '23.687.354-4', '678.687.306-11', '25')
--Testando a UNIQUE CPF
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Julia', '33.687.354-4', '263.687.306-11', '25')
--Testando a coluna num_cli com a constraint CHECK (num_cli >= 0)
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Julia', -1, '33.687.354-4', '565.687.306-11', '30')
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Julia', 100, '33.687.354-4', '565.687.306-11', '30')
SELECT TOP 10 * FROM clientes
--Testando a constraint CHECK (sexo_cli='F' OR sexo_cli='M')
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli, sexo_cli)
VALUES ('Florindo', 24, '24.687.354-4', '240.241.242-24', '24', 'G')
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli, sexo_cli)
VALUES ('Florindo', 24, '24.687.354-4', '240.241.242-24', '24', 'M')
SELECT TOP 10 * FROM clientes
--Inserindo todos os campos de um registro
INSERT INTO clientes (nome_cli, rua_cli, num_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Pedro', 'Rua das Palmeiras', 987, '10.687.354-4', '165.687.306-11', '32')
SELECT TOP 10 * FROM clientes
---------------------------------------------------------------------
--Limpando e inserindo dados
TRUNCATE TABLE clientes
GO
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli)
VALUES ('Marina', '12.450.459-3', '234.687.576-45')
INSERT INTO clientes (nome_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Joana', '23.687.354-4', '263.687.306-11', '25')
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli, sexo_cli)
VALUES ('Florindo', 24, '24.687.354-4', '240.241.242-24', '24', 'M')
INSERT INTO clientes (nome_cli, num_cli, rg_cli, cpf_cli, idade_cli)
VALUES ('Julia', 100, '33.687.354-4', '565.687.306-11', '30')
SELECT TOP 10 * FROM clientes
---------------------------------------------------------------------
--Criando e nomeando constraints explicitamente
---------------------------------------------------------------------
CREATE TABLE clientes_backup
(
cod_cli int IDENTITY,
nome_cli char(50) NOT NULL,
rua_cli char(50),
num_cli int,
rg_cli char(15) NOT NULL,
cpf_cli char(15) NOT NULL,
idade_cli char(02) CONSTRAINT DF_clientes_idade_cli DEFAULT ('0'),
sexo_cli char(01),
data_insc_cli datetime CONSTRAINT DF_clientes_data_insc_cli DEFAULT (GETDATE()),
CONSTRAINT PK_clientes_cod_cli PRIMARY KEY (cod_cli),
CONSTRAINT CH_clientes_num_cli CHECK (num_cli >= 0),
CONSTRAINT CH_clientes_sexo_cli CHECK (sexo_cli = 'F' OR sexo_cli = 'M'),
CONSTRAINT UQ_clientes_rg_cli UNIQUE (rg_cli),
CONSTRAINT UQ_clientes_cpf_cli UNIQUE (cpf_cli)
)
GO
sp_helpconstraint 'clientes_backup'
--------------------------------------------------------------------------
--Criando a tabela pedidos relacionada a tabela clientes
CREATE TABLE pedidos
(
cod_ped int IDENTITY PRIMARY KEY,
cod_cli int NOT NULL CHECK (cod_cli > 0),
val_ped dec(9,2) NOT NULL CHECK (val_ped >= 0),
CONSTRAINT FK_pedidos_cod_cli FOREIGN KEY (cod_cli) REFERENCES clientes (cod_cli)
)
GO
SELECT TOP 10 * FROM clientes
INSERT INTO pedidos (cod_cli, val_ped)
VALUES (1, 657.89), (2, 234.77), (2, 978.88)
SELECT TOP 10 * FROM pedidos
--DELETE ON CASCADE: NO ACTION
DELETE FROM clientes
WHERE cod_cli = 2
SELECT TOP 10 * FROM clientes
SELECT TOP 10 * FROM pedidos
--DELETE ON CASCADE: CASCADE
--Verificando a existência de Foreign Keys na tabela
sp_helpconstraint 'pedidos'
--Apagando a Foreign Key "FK_pedidos_cod_cli"
ALTER TABLE pedidos
DROP FK_pedidos_cod_cli
--Verificando se a Foreign Key foi deletada com sucesso
sp_helpconstraint 'pedidos'
--Recriando a Foreign Key habilitando a opção ON DELETE CASCADE
ALTER TABLE pedidos
ADD CONSTRAINT FK_pedidos_cod_cli FOREIGN KEY (cod_cli)
REFERENCES clientes (cod_cli) ON DELETE CASCADE
--Verificando se a Foreign Key foi criada com sucesso
sp_helpconstraint 'pedidos'
SELECT TOP 10 * FROM clientes
SELECT TOP 10 * FROM pedidos
--Apagando o cliente com cod_cli = 2
DELETE FROM clientes
WHERE cod_cli = 2
SELECT TOP 10 * FROM clientes
SELECT TOP 10 * FROM pedidos
--------------------------------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.check_constraints
SELECT * FROM INFORMATION_SCHEMA.referential_constraints
SELECT * FROM INFORMATION_SCHEMA.table_constraints
--------------------------------------------------------------------------
sp_helpconstraint 'pedidos'
--Desabilitando uma CONSTRAINT
ALTER TABLE pedidos
NOCHECK CONSTRAINT FK_pedidos_cod_cli
sp_helpconstraint 'pedidos'
--Inserindo um registro com a FOREIGN KEY desabilitada
INSERT INTO pedidos (cod_cli, val_ped) VALUES (100, 978.88)
select * from clientes
select * from pedidos
--Habilitando uma CONSTRAINT
ALTER TABLE pedidos
CHECK CONSTRAINT FK_pedidos_cod_cli
--Apagando uma CONSTRAINT
ALTER TABLE pedidos
DROP FK_pedidos_cod_cli
--Adicionando uma CONSTRAINT
--Verifica a consistência dos dados existentes na tabela
ALTER TABLE pedidos
ADD CONSTRAINT FK_pedidos_cod_cli FOREIGN KEY (cod_cli)
REFERENCES clientes (cod_cli)
--Adicionando uma CONSTRAINT
--NÃO verifica a consistência dos dados existentes na tabela
ALTER TABLE pedidos WITH NOCHECK
ADD CONSTRAINT FK_pedidos_cod_cli FOREIGN KEY (cod_cli)
REFERENCES clientes (cod_cli)
sp_helpconstraint 'pedidos'
CREATE TABLE locadora
(
cod_cli INT IDENTITY PRIMARY KEY,
nome_filme CHAR(30) NOT NULL,
dt_retirada SMALLDATETIME NOT NULL DEFAULT GETDATE(),
dt_entrega SMALLDATETIME
)
GO
--Criando uma constraint CHECK na coluna dt_entrega
ALTER TABLE locadora WITH CHECK
ADD CONSTRAINT CH_dt_entrega
CHECK (dt_entrega >= dt_retirada)
INSERT INTO locadora (nome_filme)
VALUES ('O Vento Levou')
SELECT TOP 10 * FROM locadora
INSERT INTO locadora (nome_filme, dt_retirada, dt_entrega)
VALUES ('Batman','2008-08-10', '2008-08-12')
SELECT TOP 10 * FROM locadora
--Testando a constraint CHECK
INSERT INTO locadora (nome_filme, dt_retirada, dt_entrega)
VALUES ('A Rocha','2008-08-14', '2008-08-12')
--------------------------------------------------------------------------
USE [master]
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE [name] = 'DB6232')
DROP DATABASE DB6232
GO
--Aula 02 - Implementing Triggers
--===================================================================--Autor : Lineu Alves Lima Filho
--Data Criação : 10/03/2009
--Descrição : Módulo 04
--===================================================================
--Versão : 1.0
--===================================================================
--Versão :
--Data Atualização :
--===================================================================
CREATE DATABASE DB6232_TR
GO
USE DB6232_TR
GO
CREATE TABLE produtos
(
cod_prod INT IDENTITY PRIMARY KEY,
nome_prod CHAR(50) NOT NULL,
val_prod DEC(9,2) CHECK (val_prod >= 0)
)
GO
--INSERT TRIGGER
CREATE TRIGGER trg_valida_ins ON produtos
FOR INSERT
AS
SELECT 'Registro(s) cadastrado(s) com sucesso:'
SELECT * FROM inserted
GO
--Inserindo 1 registro
INSERT INTO produtos VALUES ('Batata',1.99)
--Inserindo vários registros
INSERT INTO produtos VALUES ('Arroz',8.99), ('Uva',1.99),
('Soja',1.29), ('Laranja',1.09), ('Morango',3.99)
--DELETE TRIGGER
CREATE TRIGGER trg_exclui_del ON produtos
FOR DELETE
AS
SELECT 'Registro(s) excluído(s) com sucesso:'
SELECT * FROM deleted
GO
SELECT TOP 10 * FROM produtos
DELETE TOP(2) FROM produtos
--UPDATE TRIGGER
CREATE TRIGGER trg_altera_upd ON produtos
FOR UPDATE
AS
BEGIN
DECLARE @num_updated INT
SELECT @num_updated = COUNT(*) FROM deleted
SELECT 'Número de registro(s) atualizado(s) com sucesso: ' +
CONVERT(VARCHAR(03), @num_updated)
SELECT * FROM inserted
END
GO
SELECT TOP 10 * FROM produtos
UPDATE produtos
SET val_prod = 9.99
WHERE cod_prod = 6
UPDATE produtos
SET val_prod = 1.99
WHERE cod_prod > 4
--Criando sistema de auditoria com triggers
CREATE TABLE auditoria
(
data SMALLDATETIME DEFAULT GETDATE(),
nome_user VARCHAR(20) DEFAULT USER_NAME(),
computador VARCHAR(20) DEFAULT HOST_NAME(),
registro INT,
tabela CHAR(15)
)
GO
CREATE TRIGGER trg_auditoria_del ON produtos
FOR DELETE
AS
INSERT INTO auditoria (tabela, registro)
SELECT 'produtos', cod_prod FROM deleted
GO
DELETE FROM produtos
WHERE cod_prod = 4
SELECT TOP 10 * FROM auditoria
SELECT TOP 10 * FROM produtos
--INSTEAD OF TRIGGER
CREATE TRIGGER trg_auditoria_del_upd ON auditoria
INSTEAD OF DELETE, UPDATE
AS
BEGIN
PRINT 'Procure o responsável pela aplicação!'
END
GO
DELETE FROM auditoria
SELECT TOP 10 * FROM auditoria
--Verificando se as Nested Triggers estão habilitadas
sp_configure
--Habilitando Nested Triggers
sp_configure 'nested triggers',1
RECONFIGURE
--Aula 03 - Atividade
USE DB6232_TR
GO
CREATE TABLE produtos_backup
(
data DATETIME DEFAULT GETDATE(),
nome_user VARCHAR(20) DEFAULT USER_NAME(),
computador VARCHAR(20) DEFAULT HOST_NAME(),
comando CHAR(01),
cod_prod INT,
nome_prod CHAR(50),
val_prod DEC(9,2)
)
GO
--Trigger para registros DELETADOS
CREATE TRIGGER trg_produtos_backup_del ON produtos
FOR DELETE
AS
INSERT INTO produtos_backup (cod_prod, nome_prod, val_prod, comando)
SELECT cod_prod, nome_prod, val_prod, 'D' FROM deleted
GO
SELECT * FROM produtos_backup
--Trigger para registros ATUALIZADOS
CREATE TRIGGER trg_produtos_backup_upd ON produtos
FOR UPDATE
AS
INSERT INTO produtos_backup (cod_prod, nome_prod, val_prod, comando)
SELECT *, 'U' FROM deleted
INSERT INTO produtos_backup (cod_prod, nome_prod, val_prod, comando)
SELECT *, 'U' FROM inserted
GO
SELECT * FROM produtos_backup
--Trigger para registros INSERIDOS
CREATE TRIGGER trg_produtos_backup_ins ON produtos
FOR INSERT
AS
INSERT INTO produtos_backup (cod_prod, nome_prod, val_prod, comando)
SELECT cod_prod, nome_prod, val_prod, 'I' FROM inserted
GO
SELECT * FROM produtos_backup
Nenhum comentário:
Postar um comentário