
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[GetOwnDepLink]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.[GetOwnDepLink]

GO

/*
c Кейсистемс (Keysystems), 2005
*/
CREATE   FUNCTION [GetOwnDepLink]()
RETURNS int
AS
BEGIN
RETURN (SELECT TOP 1 LINK FROM dbo.DEPARTMENTS WHERE TYPE = 4)
END



GO


GRANT EXECUTE ON dbo.[GetOwnDepLink] TO PUBLIC

GO


--Измение колонки NAME таблицы report_coloumns
ALTER TABLE [dbo].[REPORT_COLOUMNS] ALTER COLUMN [Name] varchar(250) NULL 
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[OBJECT_VAR_DELETE_LOG]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER dbo.[OBJECT_VAR_DELETE_LOG]
GO

IF exists(select * from sysobjects where id = object_id('[dbo].[_OV_LOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[_OV_LOG]
GO

--Таблица report_kbk_hist
IF not exists(select * from sysobjects where id = object_id('[dbo].[REPORT_KBK_HIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[REPORT_KBK_HIST] (
	[LINK_UP] int NOT NULL ,
	[DATE] datetime NOT NULL ,
	[NAME] varchar(254) NOT NULL ,
	[LONG_NAME] varchar(8000) NULL )
GO

	GRANT SELECT, UPDATE, DELETE ON dbo.[report_kbk_hist] TO PUBLIC
	
GO

--Расширенные свойства--
--Индексы
--Констрэйнты

IF  NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[PK_REPORT_KBK_HIST]'))
ALTER TABLE [dbo].[REPORT_KBK_HIST] 
ADD CONSTRAINT [PK_REPORT_KBK_HIST] PRIMARY KEY CLUSTERED ([LINK_UP], [DATE])
GO

IF  NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[FK_REPORT_KBK_HIST_REPORT_KBK_HIST]'))
ALTER TABLE [dbo].[REPORT_KBK_HIST] 
ADD CONSTRAINT [FK_REPORT_KBK_HIST_REPORT_KBK_HIST] FOREIGN KEY ([LINK_UP]) REFERENCES [dbo].[REPORT_KBK] ([link])
 ON DELETE CASCADE
 ON UPDATE CASCADE
GO

IF  NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[DF_REPORT_KBK_HIST_NAME]'))
ALTER TABLE [dbo].[REPORT_KBK_HIST] 
ADD CONSTRAINT [DF_REPORT_KBK_HIST_NAME] DEFAULT ('') FOR [NAME]
GO

IF  NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[DF_REPORT_KBK_HIST_LONG_NAME]'))
ALTER TABLE [dbo].[REPORT_KBK_HIST] 
ADD CONSTRAINT [DF_REPORT_KBK_HIST_LONG_NAME] DEFAULT ('') FOR [LONG_NAME]
GO
--Триггеры
