Skrypt T-SQLa tworzący obiekty w bazie danych i wykonujący kilka operacji testowych.

/*
Biblioteka
*/
CREATE DATABASE Biblioteka
GO
USE Biblioteka
GO

/*
tworzenie tabel
*/
CREATE TABLE Ksiazka
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  idAutor  INT,
  idkategoria INT,
  tytul VARCHAR(200)
)

CREATE TABLE Autor
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  imie  VARCHAR(30),
  nazwisko VARCHAR(30)
)

CREATE TABLE Kategoria
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  nazwa VARCHAR(50)
)

CREATE TABLE Czytelnik
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  imie  VARCHAR(30),
  nazwisko VARCHAR(30)
)

/*
  pole typ okresla rodzaj wypozyczenia
  0 - wypozyczenie
  1 - zwrot
  2 - likwidacja pozycji (np. z powodu zniszczenia)
*/
CREATE TABLE Wypozyczenia
(
  dataWyp DATETIME DEFAULT getdate(),
  idKsiazka INT,
  idCzytelnik INT,
  typ INT
)

CREATE TABLE Stan
(
  idKsiazka INT,
  ilosc INT DEFAULT 0,
  stanBiezacy INT DEFAULT 0
)
GO

/*
Funkcja zwracajaca imie i nazwisko autora dla podanego id autora
*/
CREATE FUNCTION autorImieNazwisko(@idAutor INT)
RETURNS VARCHAR(65)
AS
BEGIN
  DECLARE @imieNazwisko  VARCHAR(65)
  DECLARE @imie  VARCHAR(30)
  DECLARE @nazwisko  VARCHAR(30)

  SELECT @imie = imie, @nazwisko = nazwisko FROM Autor WHERE id = @idAutor
  
  SET @imieNazwisko = @imie + ' ' + @nazwisko
  RETURN (@imieNazwisko)  
END
GO

/*
Funkcja zwracajaca nazwe kategorii dla podanego id
*/
CREATE FUNCTION kategoriaNazwa(@idKategoria INT)
RETURNS VARCHAR(50)
AS
BEGIN
  DECLARE @nazwa  VARCHAR(50)

  SELECT @nazwa = nazwa FROM Kategoria WHERE id = @idKategoria
  
  RETURN (@nazwa)  
END
GO

/*
Funkcja zwracajaca wyp wypozyczenia
  0 - wypozyczenie
  1 - zwrot
  2 - likwidacja pozycji (np. z powodu zniszczenia)

*/
CREATE FUNCTION typWypozyczenia(@typ INT)
RETURNS VARCHAR(20)
AS
BEGIN
  DECLARE @nazwa  VARCHAR(20)
  
  SET @nazwa =
  CASE @typ
    WHEN 0 THEN 'wypozyczenia'
    WHEN 1 THEN 'zwrot'
    WHEN 2 THEN 'likwidacja'
  END
  
  RETURN (@nazwa)  
END
GO

/*
procedura dodania nowej ksiazki
*/
CREATE PROCEDURE ksiazkaDodaj
  @idAutor INT,
  @idKategoria INT,
  @tytul VARCHAR(200),
  @ilosc INT
AS
BEGIN
  DECLARE @idKsiazka INT
  
  /* dodanie do tabeli Ksiazka */
  INSERT INTO Ksiazka (idAutor, idKategoria, tytul)
    VALUES (@idAutor, @idKategoria, @tytul)

  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    SET @idKsiazka = @@IDENTITY
    INSERT INTO Stan VALUES(@idKsiazka, @ilosc, @ilosc)
  END
END
GO

/*
procedura wypozyczenia ksiazki (transakcja)
*/
CREATE PROCEDURE ksiazkaWypozycz
  @idKsiazka INT,
  @idCzytelnik INT
AS
BEGIN

  BEGIN TRANSACTION
  
    INSERT INTO Wypozyczenia (idKsiazka, idCzytelnik, typ)
      VALUES (@idKsiazka, @idCzytelnik, 0)

    IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
    BEGIN
      UPDATE Stan SET stanBiezacy = stanBiezacy - 1 WHERE idKsiazka = @idKsiazka

      IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
      BEGIN
        COMMIT TRANSACTION
      END
      ELSE
      BEGIN
        ROLLBACK TRANSACTION
      END
    END
    ELSE
    BEGIN
      ROLLBACK TRANSACTION
    END
END
GO

/*
procedura zwrotu ksiazki (transakcja)
*/
CREATE PROCEDURE ksiazkaZwroc
  @idKsiazka INT,
  @idCzytelnik INT
AS
BEGIN

  BEGIN TRANSACTION
  
    INSERT INTO Wypozyczenia (idKsiazka, idCzytelnik, typ)
      VALUES (@idKsiazka, @idCzytelnik, 1)

    IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
    BEGIN
      UPDATE Stan SET stanBiezacy = stanBiezacy + 1 WHERE idKsiazka = @idKsiazka

      IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
      BEGIN
        COMMIT TRANSACTION
      END
      ELSE
      BEGIN
        ROLLBACK TRANSACTION
      END
    END
    ELSE
    BEGIN
      ROLLBACK TRANSACTION
    END
END
GO

/*
  trigger sprawdzajacy czy stanBiezacy nie spadl ponizej zera, ani nie
  przekroczyl ilosci
*/
CREATE TRIGGER sprawdzStan
ON Stan
AFTER UPDATE
AS
  -- czy ilosc nie spadla ponizej zera
  SELECT stanBiezacy FROM Stan WHERE stanBiezacy < 0
  IF (@@ROWCOUNT > 0)
  BEGIN
    RAISERROR('Proba wypozyczenia ksiazki, ktorej nie ma na stanie', 16, 1)
    ROLLBACK TRANSACTION
  END

  -- czy czasami nie zwrocono wicej niz jest ksiazek
  SELECT idKsiazka FROM Stan WHERE (stanBiezacy > ilosc)
  IF (@@ROWCOUNT > 0)
  BEGIN
    RAISERROR('Proba zwrotu ksiazki, ktorej stan jest juz maksymalny', 16, 1)
    ROLLBACK TRANSACTION
  END
GO

/*
  widok z lista ksiazek i ich stanem
*/
CREATE VIEW ksiazkaLista
AS  
  SELECT
    Ksiazka.tytul,
    dbo.autorImieNazwisko(Ksiazka.idAutor) AS autor,
    dbo.kategoriaNazwa(Ksiazka.idkategoria) AS kategoria,
    Stan.ilosc,
    Stan.stanBiezacy
  FROM
    Ksiazka, Stan
  WHERE
    Ksiazka.id = Stan.idKsiazka
GO

/*
  widok z lista wypozyczen
*/
CREATE VIEW wypozyczeniaLista
AS  
  SELECT
    Wypozyczenia.dataWyp,
    Ksiazka.tytul,
    Czytelnik.imie + ' ' + Czytelnik.nazwisko AS czytelnikIN,
    dbo.typWypozyczenia(typ) AS typ
  FROM
    Wypozyczenia, Ksiazka, Czytelnik
  WHERE
    Wypozyczenia.idKsiazka = Ksiazka.id AND
    WYpozyczenia.idCzytelnik = Czytelnik.id
GO

/*
  dodanie kilku kategorii
*/
INSERT INTO Kategoria (nazwa) VALUES ('historia')
INSERT INTO Kategoria (nazwa) VALUES ('ekonomia')
INSERT INTO Kategoria (nazwa) VALUES ('fantasy')
INSERT INTO Kategoria (nazwa) VALUES ('informatyka')
GO

/*
  dodanie kilku autorow
*/
INSERT INTO Autor (imie, nazwisko) VALUES ('JRR', 'Tolkien')
INSERT INTO Autor (imie, nazwisko) VALUES ('Steve', 'McConnell')
INSERT INTO Autor (imie, nazwisko) VALUES ('Paweł', 'Jasienica')
GO

/*
dodanie czytelnikow
*/
INSERT INTO Czytelnik (imie, nazwisko) VALUES ('Jaś', 'Nowak')
INSERT INTO Czytelnik (imie, nazwisko) VALUES ('Ola', 'Malinowska')
GO

/*
dodanie ksiazek
*/
EXECUTE ksiazkaDodaj 1, 3, 'Władca pierścieni', 3
EXECUTE ksiazkaDodaj 2, 4, 'Code complete', 1
EXECUTE ksiazkaDodaj 3, 1, 'Historia Polski', 2
GO

/* wypozyczenia */
EXECUTE ksiazkaWypozycz 1, 1
GO
EXECUTE ksiazkaWypozycz 2, 2
GO
/* wypozyczeni 2x ksiazki o stanie 1 spowoduje blad */
EXECUTE ksiazkaWypozycz 2, 2
GO

/* zwrot */
EXECUTE ksiazkaZwroc 2, 2
GO

/* przejrzenie wynikow */
SELECT * FROM ksiazkaLista
GO
SELECT * FROM wypozyczeniaLista
GO