Vistas, gatilhos e controlo de acessos

Vistas: consultas com nome

Uma vista virtual guarda a pergunta, não os resultados: alterar a base muda a resposta. Uma materializada guarda resultados e pede atualização; PostgreSQL tem-nas, SQLite não. A definição também não diz como traduzir escritas: pedir total 120 não indica que linhas mudar.

• Em SQLite, escreve através de vistas com gatilhos INSTEAD OF; nada é automático.
• Em PostgreSQL, vistas simples atualizam-se sozinhas; WITH CHECK OPTION pode exigir que a linha continue no filtro da vista.

Gatilhos: quando disparam e o que veem

Um gatilho segue evento (INSERT, UPDATE, DELETE), condição WHEN opcional e ação. Em SQLite é por linha: dez linhas disparam dez vezes.

BEFORE corre antes, AFTER depois, INSTEAD OF substitui a operação na vista. UPDATE OF stock reage à coluna constar da instrução, mesmo sem mudança de valor: confirma a diferença em WHEN.

CREATE TABLE AlteracaoStock(
idProduto INTEGER, anterior INTEGER, novo INTEGER
);
CREATE TRIGGER AuditarStock
AFTER UPDATE OF stock ON Produto
FOR EACH ROW WHEN OLD.stock <> NEW.stock
BEGIN
INSERT INTO AlteracaoStock VALUES (NEW.id, OLD.stock, NEW.stock);
END;
UPDATE Produto SET stock = stock - 2 WHERE id = 10;
UPDATE Produto SET stock = stock WHERE id = 10;
SELECT * FROM AlteracaoStock;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

Uma linha (produto 10, 20 para 18); a segunda instrução dispara mas a condição trava-a. Prefere CHECK, UNIQUE e estrangeiras quando exprimem a regra. Um limite de encomendas tem de cobrir inserções e mudanças de cliente; mínimos pedem eliminações. Evita ciclos e ordens de gatilhos não garantidas.

RAISE(ABORT, ...) desfaz a instrução sem necessariamente abortar a transação; RAISE(ROLLBACK, ...) termina-a.

Reservar com dois gatilhos

Uma estrangeira por participante não prova que o par é compatível. Para inserções de usos: BEFORE rejeita par incompatível ou stock insuficiente com RAISE(ABORT,...); AFTER debita o uso aceite. Um erro no lote desfaz também débitos de linhas anteriores do mesmo lote.

Ao mudar quantidades da mesma peça: disponibilidade stock + OLD.qtd, variação OLD.qtd - NEW.qtd. Ao trocar de peça: devolve à antiga, valida e debita a nova. Apagar histórico consumido não repõe stock; cancelar reserva pode repor. Gatilhos só de inserção não cobrem alterações nem eliminações.

Quem pode o quê

Autenticação identifica; autorização permite. Menor privilégio sempre.

• SQLite é embebido: sem utilizadores, GRANT, REVOKE ou RLS. Vale o ficheiro e a aplicação; uma vista não esconde colunas de quem abre a base.
• Em PostgreSQL, papéis juntam privilégios; GRANT dá, REVOKE tira, sem apagar outras vias de acesso. Propriedade e GRANT OPTION são privilégios à parte.
• RLS filtra linhas além dos privilégios: USING nas existentes, WITH CHECK nas novas. Não concede SELECT/UPDATE. Sem política aplicável, nega; superutilizador, BYPASSRLS e normalmente o proprietário contornam-na, salvo FORCE ROW LEVEL SECURITY. current_user é a conta SQL, não o utilizador da aplicação.

Parametriza consultas: código e valores seguem separados (WHERE email = ?). Colunas de ordenação e ASC/DESC vêm de listas fechadas, não de parâmetros.

Políticas RLS e teste com outro papel.