Материально-техническое обеспечение учебного процесса

 CONSTRAINT [PK_postavshiki] PRIMARY KEY CLUSTERED

(

      [id_post] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[inventory]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[inventory](

      [inv_nomer] [varchar](5) NOT NULL,

      [kod_tovara] [varchar](6) NULL,

      [name_oborudovan] [varchar](30) NOT NULL,

      [mesto] [varchar](12) NULL,

      [data_priobreteniya] [datetime] NULL,

      [opisanie_oborudovan] [text] NULL,

 CONSTRAINT [PK_оборудование] PRIMARY KEY CLUSTERED

(

      [inv_nomer] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[materials]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[materials](

      [kod_tovara] [varchar](6) NOT NULL,

      [name_tovara] [varchar](30) NOT NULL,

      [opisanie_tov] [text] NULL,

      [ostatok] [int] NULL,

      [potreb_na_nach_mes] [int] NULL,

      [nehvatka] [int] NULL,

      [ed_izmeren] [varchar](5) NULL,

      [summa] [money] NULL,

 CONSTRAINT [PK_materials_1] PRIMARY KEY CLUSTERED

(

      [kod_tovara] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

GO 

SET ANSI_NULLS ON

GO 

SET QUOTED_IDENTIFIER ON

GO 

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[dobavlen_materiala]'))

EXEC dbo.sp_executesql @statement = N'CREATE trigger [dbo].[dobavlen_materiala] on [dbo].[materials] For Insert

as

declare @k_tov varchar(6)

declare @name_t varchar (30)

declare @opis_t varchar(80)

declare @izmeren_t varchar(5)

select @name_t=name_tovara from inserted

select @k_tov=kod_tovara, @opis_t=opisanie_tov, @izmeren_t=ed_izmeren from tovar where @name_t=name_tovara

insert materials values (@k_tov, @name_t, @opis_t, 0, 0, 0, @izmeren_t, 0)

'

GO 

SET ANSI_NULLS ON

GO 

SET QUOTED_IDENTIFIER ON

GO 

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[podshet_potrebnosti]'))

EXEC dbo.sp_executesql @statement = N'CREATE trigger [dbo].[podshet_potrebnosti] on [dbo].[materials] For update

AS

/*If Exists (Select * From Deleted)

begin*/

declare @kod varchar(6)

select @kod=kod_tovara from Deleted

/*end*/

declare @nehvatka int

declare @ostatok int

declare @potrebnost int