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

/*
baza: Firma
*/

CREATE DATABASE firma
GO
USE firma
GO

/*
tworzenie tabeli towar
*/
CREATE TABLE towar (
  id    int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  kod   varchar(20),
  nazwa varchar(50),
  cena  money
)

/*
tworzenie tabeli kontrahent
*/
CREATE TABLE kontrahent (
  id    int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  kod   varchar(20),
  nazwa varchar(50)
)

/*
tworzenie tabeli dokument
*/
CREATE TABLE dokument (
  id    int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  typ   char(2),
  numer int,
  data_wystawienia datetime DEFAULT getdate(),
  identyfikator varchar(20),
  id_firma  int,
  wartosc   money
)

/*
tworzenie tabeli dokument_pozycja
*/
CREATE TABLE dokument_pozycja (
  id    int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  id_dokument int,
  id_towar  int,
  ilosc   float,
  cena    money,
  wartosc money
)

/*
  tworzenie tabeli numer
*/
CREATE TABLE numer (
  typ char(2),
  numer int
)
GO

/*
  procedura dodajaca nowy towar
*/
CREATE PROCEDURE dodaj_towar
  @kod varchar(20),
  @nazwa varchar(50),
  @cena money
AS
BEGIN
  INSERT INTO towar
    (kod , nazwa , cena  )
    VALUES (@kod , @nazwa , @cena  )

  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    RETURN (@@IDENTITY)
  END
  ELSE
  BEGIN
    RETURN(0)
  END
END
GO

/*
  procedura dodajaca nowego kontrahenta
*/
CREATE PROCEDURE dodaj_kontrahent
  @kod varchar(20),
  @nazwa varchar(50)
AS
BEGIN
  INSERT INTO kontrahent
    (kod , nazwa)
    VALUES (@kod , @nazwa)

  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    RETURN (@@IDENTITY)
  END
  ELSE
  BEGIN
    RETURN(0)
  END
END

GO

/*
  procedura dodajaca naglowek dokumentu
*/
CREATE PROCEDURE dodaj_dokument
  @typ char(2),
  @kod_kt varchar(20)
AS
BEGIN

  DECLARE @id_kt int
  DECLARE @numer int
  
  /* pobranie biezacego numeru */
  SELECT @numer = numer FROM numer WHERE typ = @typ

  /* aktualizacja numeracji */
  UPDATE numer SET numer = numer + 1 WHERE typ = @typ

  /* pobranie id firmy */
  SELECT @id_kt = id FROM kontrahent WHERE kod = @kod_kt

  INSERT INTO dokument (typ, numer, data_wystawienia, identyfikator, id_firma, wartosc)
    VALUES (@typ, @numer, getdate(), dbo.utworz_identyfikator(@typ), @id_kt, 0.0)
    
  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    RETURN (@@IDENTITY)
  END
  ELSE
  BEGIN
    RETURN(0)
  END    
END

GO

/*
  procedura dodajaca nowego pozycje dokumentu
*/
CREATE PROCEDURE dodaj_dokument_pozycja
  @id_dokument int,
  @kod int,
  @ilosc float
AS
BEGIN

  DECLARE @cena money
  DECLARE @wartosc money
  DECLARE @id_towar int
  
  /* pobranie ceny i id towaru*/
  SELECT @cena = cena FROM towar WHERE kod = @kod
  SELECT @id_towar = id FROM towar WHERE kod = @kod
  
  SET @wartosc = @ilosc * @cena

  INSERT INTO dokument_pozycja (id_dokument, id_towar, ilosc, cena, wartosc)
    VALUES (@id_dokument, @id_towar, @ilosc, @cena, @wartosc)
END

GO

/*
utworzenie widoku do przegladania dokumentow
*/
CREATE VIEW lista_dokument
AS  
    SELECT
      dokument.data_wystawienia,
      dokument.identyfikator,
      kontrahent.nazwa,
      dokument.wartosc
    FROM
      dokument, kontrahent
    WHERE dokument.id_firma = kontrahent.id
     
GO

/*
utworzenie widoku do przegladania pozycji
*/
CREATE VIEW lista_pozycja
AS  
    SELECT
      towar.nazwa,
      dokument_pozycja.cena,
      dokument_pozycja.ilosc,
      dokument_pozycja.wartosc
    FROM
      dokument_pozycja, towar
    WHERE dokument_pozycja.id_towar = towar.id
     
GO
/*
utworzenie widoku z biezaca data, by mozna bylo uzyc go w funkcji tworzenia identyfikatora
getdate() nie mozna
*/
CREATE VIEW biezaca_data
AS  
    SELECT bdata = GETDATE() 
GO

/* dodanie danych do tabel */

EXEC dodaj_towar '0001', 'napoj gazowany 1l.', 2.50
EXEC dodaj_towar '0002', 'czekolada mleczna', 4.50
EXEC dodaj_towar '0003', 'sok pomaranczowy', 3.90
GO

EXEC dodaj_kontrahent '0001', 'Firma Handlowa ALA'
EXEC dodaj_kontrahent '0002', 'Firma Handlowa OLA'
GO

INSERT INTO numer VALUES ('FV', 1)
INSERT INTO numer VALUES ('PA', 1)
INSERT INTO numer VALUES ('FZ', 1)
GO

/*
funkcja zwracajaca biezacy numer dokumentu jako znakowy identyfikator
w formacie numer/rok
*/
CREATE FUNCTION utworz_identyfikator (@typ char(2))
RETURNS varchar(20)
AS
BEGIN
  DECLARE @numer int
  DECLARE @identyfikator varchar(20)
  DECLARE @rok as int
  DECLARE @data_biezaca datetime
  
  SELECT @data_biezaca = bdata FROM biezaca_data
  SET @rok = DATEPART(year, @data_biezaca)
  
  SELECT @numer = numer FROM numer WHERE typ = @typ
  
  /* utworzeniu identyfikatora */
  SET @identyfikator = CAST(@numer AS varchar(15)) + '/' + CAST(@rok AS char(4))

  -- SELECT @pelny_nr
  RETURN (@identyfikator)  
END
GO

/*
  trigger przy usuwaniu dokumentu usuwa rowniez nalezace do niego pozycje
*/

CREATE TRIGGER usun_pozycje
ON dokument
FOR DELETE
AS
  DELETE FROM dokument_pozycja WHERE id_dokument = (SELECT id FROM Deleted)
GO

/*
  dodanie dokumentu FV jako transakcja
*/

  BEGIN TRANSACTION

  DECLARE @numer int
  DECLARE @id int
  DECLARE @wart_dok money
  
  EXEC @id = dodaj_dokument 'FV', '0001'

  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END
  
  /* dodanie pozycji */
  EXEC dodaj_dokument_pozycja @id, 1, 5
  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END
  /* dodanie pozycji */
  EXEC dodaj_dokument_pozycja @id, 2, 3
  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END
  /* dodanie pozycji */
  EXEC dodaj_dokument_pozycja @id, 3, 12
  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END

  /* aktualizacja wartosci dokumentu */
  SELECT @wart_dok = SUM(wartosc) FROM dokument_pozycja WHERE id_dokument = @id
  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END

  UPDATE dokument SET wartosc = @wart_dok WHERE id = @id
  IF (( @@ERROR > 0 ) AND ( @@ROWCOUNT = 0))
  BEGIN
    GOTO on_error
  END
  
  COMMIT TRANSACTION
  GOTO on_fine

on_error:

  ROLLBACK TRANSACTION

on_fine:

  PRINT 'dokument dodany'