Microsoft SQL Server: 09.00.4053
Microsoft OLE DB Provider for SQL Server: 08.00.1132
Представление:
Код: Выделить всё
CREATE VIEW [dbo].[vwTask] with view_metadata as
select m.*, t.idCFU_1, t.idCFU_2, t.nCFUprc_1, t.nCFUprc_2, t.nControl, t.idTaskCategory
from rtMain m
inner join rtTask t on t.idMain = m.idMain
Триггеры:
Код: Выделить всё
CREATE TRIGGER [dbo].[TID_vwTask]
ON [dbo].[vwTask]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
delete from rtMain where idMain in (select idMain from deleted);
if @@error <> 0
begin
raiserror('Ошибка удаления задачи из списка.', 16, 1);
rollback;
return;
end;
return;
END
Код: Выделить всё
CREATE TRIGGER [dbo].[TII_vwTask]
ON [dbo].[vwTask]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
if (select count(*) from inserted) > 1
begin
raiserror('Нельзя добавлять больше одной задачи за раз', 16, 1);
rollback;
return;
end;
declare @IDs table (id int);
insert into rtMain (
sName, idPerson, idRequester, idParent, idCFU, idDepartment, nPrior, nType, dPlan, dFloat, dFact, dBegin, sNotes, sOrderNumb,
dOrderDate, idBudget, idBoss, sComment, sIncOrderNumb, dIncOrderDate, bNotice, nPlan, sPatchSvn)
output inserted.idMain into @IDs
select sName, idPerson, idRequester, idParent, idCFU, idDepartment, nPrior, 2, dPlan, dFloat, dFact, dBegin, sNotes, sOrderNumb,
dOrderDate, idBudget, idBoss, sComment, sIncOrderNumb, dIncOrderDate, bNotice, nPlan, sPatchSvn
from inserted;
if @@error <> 0
begin
raiserror('Задача не добавлена в таблицу rtMain', 16, 1);
rollback;
return;
end;
declare @id int;
select @id = id from @IDs;
exec coreIntToContext @iValue = @id;
insert into rtTask (idMain, nControl, idCFU_1, idCFU_2, nCFUprc_1, nCFUprc_2, idTaskCategory)
select @id, nControl, idCFU_1, idCFU_2, nCFUprc_1, nCFUprc_2, idTaskCategory from inserted;
if @@error <> 0
begin
raiserror('Задача не добавлена в таблицу rtTask', 16, 1);
rollback;
return;
end;
declare @idState int;
select @idState = idState from infState where sName = 'Не начата';
insert into repMain (
idMain, idState, dState, sState, dBeginRep, dEndRep, sCreateUser, dCreateDate, nDays)
values (
@id, @idState, cast(floor(cast(getdate() as float)) as datetime), 'Отчет о создании.', '19000909', '19000909', suser_sname(), getdate(), 0)
if @@error <> 0
begin
raiserror('Не добавлен отчёт о создании задачи', 16, 1);
rollback;
return;
end;
return;
END
Код: Выделить всё
CREATE TRIGGER [dbo].[TIU_vwTask]
ON [dbo].[vwTask]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
update rtMain
set sName = I.sName, idPerson = I.idPerson, idRequester = I.idRequester, idParent = I.idParent, idCFU = I.idCFU,
idDepartment = I.idDepartment, nPrior = I.nPrior, dPlan = I.dPlan, dFloat = I.dFloat, dFact = I.dFact, dBegin = I.dBegin,
sNotes = I.sNotes, sOrderNumb = I.sOrderNumb, dOrderDate = I.dOrderDate, idBudget = I.idBudget, idBoss = I.idBoss,
sComment = I.sComment, sIncOrderNumb = I.sIncOrderNumb, dIncOrderDate = I.dIncOrderDate, bNotice = I.bNotice,
nPlan = I.nPlan, sPatchSvn = I.sPatchSvn
from inserted I
where I.idMain = rtMain.idMain;
if @@error != 0
begin
raiserror('Ошибка при сохранении данных задачи в таблице rtMain.', 16, 1);
rollback;
return;
end;
update rtTask
set nControl = I.nControl, idCFU_1 = I.idCFU_1, idCFU_2 = I.idCFU_2, nCFUprc_1 = I.nCFUprc_1, nCFUprc_2 = I.nCFUprc_2,
idTaskCategory = I.idTaskCategory
from inserted I
where I.idMain = rtTask.idMain;
if @@error != 0
begin
raiserror('Ошибка при сохранении данных задачи в таблице rtTask.', 16, 1);
rollback;
return;
end;
return;
END
Таблицы:
Код: Выделить всё
CREATE TABLE [dbo].[rtMain](
[idMain] [int] IDENTITY(1,1) NOT NULL,
[sName] [varchar](255) NULL,
[idPerson] [int] NULL,
[idRequester] [int] NULL,
[idParent] [int] NULL,
[idCFU] [int] NULL,
[idDepartment] [int] NULL,
[nPrior] [int] NULL,
[nType] [smallint] NULL,
[dPlan] [datetime] NULL,
[dFloat] [datetime] NULL,
[dFact] [datetime] NULL,
[dBegin] [datetime] NULL,
[sNotes] [varchar](5000) NULL,
[sOrderNumb] [varchar](800) NULL,
[dOrderDate] [datetime] NULL,
[idOld] [int] NULL,
[idBudget] [int] NULL,
[dCreateDate] [datetime] NOT NULL DEFAULT (getdate()),
[sCreateLogin] [varchar](50) NOT NULL DEFAULT (suser_sname()),
[idBoss] [int] NULL,
[sComment] [varchar](5000) NULL,
[sIncOrderNumb] [varchar](800) NULL,
[dIncOrderDate] [datetime] NULL,
[dEditDate] [datetime] NOT NULL DEFAULT (getdate()),
[sEditLogin] [varchar](35) NOT NULL DEFAULT (suser_sname()),
[bNotice] [tinyint] NULL,
[nPlan] [tinyint] NULL,
[sPatchSvn] [varchar](255) NULL,
[xRecTime] [timestamp] NULL,
CONSTRAINT [PK_rtMain] PRIMARY KEY CLUSTERED
(
[idMain] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Код: Выделить всё
CREATE TABLE [dbo].[rtTask](
[idMain] [int] NOT NULL,
[idCFU_1] [int] NULL,
[idCFU_2] [int] NULL,
[nCFUprc_1] [int] NULL,
[nCFUprc_2] [int] NULL,
[nControl] [tinyint] NOT NULL CONSTRAINT [DF_rtTask_nControl] DEFAULT (0),
[idTaskCategory] [int] NULL,
[sLink] [varchar](255) NULL,
[sID] [varchar](255) NULL,
[idMask] [int] NULL,
CONSTRAINT [PK_rtTask] PRIMARY KEY CLUSTERED
(
[idMain] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]