Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Доброго времени суток уважаемые форумчане!
Нужно помощь в написании триггера вставки и обновления для одного поля таблицы.
Собственно структура нужных таблиц:

CREATE TABLE InformationAboutDoctors
(InformationAboutDoctorID int IDENTITY NOT NULL PRIMARY KEY,
Surname nvarchar(30) NOT NULL,
Name nvarchar(20) NOT NULL,
Patronymic nvarchar(30) NOT NULL,
Sex nvarchar(1) NOT NULL,
Age tinyint NOT NULL,
MedicalDegree nvarchar(20) NOT NULL,
Experience tinyint NOT NULL,
Salary smallmoney NOT NULL DEFAULT 0,
ProfessionMedicalStaffID int NOT NULL REFERENCES ProfessionMedicalStaffs(ProfessionMedicalStaffID),
CONSTRAINT InformationAboutDoctorsSex CHECK (Sex IN('м','ж')),
CONSTRAINT InformationAboutDoctorsMedicalDegree CHECK (MedicalDegree IN('Кандидат','Доктор')))


CREATE TABLE ProfessionMedicalStaffs
(ProfessionMedicalStaffID int IDENTITY NOT NULL PRIMARY KEY,
Profession nvarchar(30) NOT NULL,
AdmissionForSurgery nvarchar(3) NOT NULL,
HarmfulConditions nvarchar(3) NOT NULL,
MinRate smallmoney NOT NULL,
CONSTRAINT ProfessionMedicalStaffsProfessions CHECK (Profession IN('Хирург','Терапевт','Невропатолог','Окулист','Стоматолог','Рентгенолог','Гинеколог','Диетолог','Эпидемиолог','Психиатр','Педиатр')),
CONSTRAINT ProfessionMedicalStaffsAdmissions CHECK (AdmissionForSurgery IN('Да','Нет')),
CONSTRAINT ProfessionMedicalStaffsHarmfulConditions CHECK (HarmfulConditions IN('Да','Нет')))


Полем для вычисления является Salary(зарплата) в таблице InformationAboutDoctors. Изначально я его устанавливаю в 0, но после вставки либо изменении полей HarmfulConditions(Вредные условия), MinRate(Минимальная ставка) данных в таблице ProfessionMedicalStaffs, и MedicalDegree(Научная степень), Experience(Стаж) данных в таблице InformationAboutDoctors оно должно пересчитываться.

Зарплата зависит от минимальной ставки + % от стажа (допустим каждые 5 лет процент увеличивается на 5) + % научной степени(кандидат - 10%, доктор - 15%) + % за вредные условия(если вредные условия - то 10%, если нет - то 0%)

Буду очень благодарен за код, а то уже 3 дня бьюсь с этим заданием...
P.S. Если что непонятно написал, то скажи где и я попробую перефразировать.
14 дек 11, 21:01    [11767613]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
А что именно не получается?
14 дек 11, 21:23    [11767708]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Я новичок в базах данных и пишу пока ужасно плохо!!!
Вот собственно сабж:
CREATE TRIGGER UpdSalaryForDoc
ON dbo.InformationAboutDoctors AFTER INSERT, UPDATE 
AS
BEGIN
	DECLARE @InterestRate int
	DECLARE @SurchargeForHazardous int
	DECLARE @AllowanceForTheDegree int
	SELECT InformationAboutDoctors.Experience
	FROM InformationAboutDoctors
	IF(InformationAboutDoctors.Experience > 0 AND InformationAboutDoctors.Experience < 5)
		SET @InterestRate = 1
	ELSE IF(InformationAboutDoctors.Experience >= 5 AND InformationAboutDoctors.Experience < 10)
		SET @InterestRate = 5
	ELSE IF(InformationAboutDoctors.Experience >= 10 AND InformationAboutDoctors.Experience < 15)
		SET @InterestRate = 10
	ELSE IF(InformationAboutDoctors.Experience >= 15 AND InformationAboutDoctors.Experience < 20)
		SET @InterestRate = 15
	ELSE IF(InformationAboutDoctors.Experience >= 20 AND InformationAboutDoctors.Experience < 25)
		SET @InterestRate = 20
	ELSE IF(InformationAboutDoctors.Experience >= 25 AND InformationAboutDoctors.Experience < 30)
		SET @InterestRate = 25
	ELSE IF(InformationAboutDoctors.Experience >= 30 AND InformationAboutDoctors.Experience < 35)
		SET @InterestRate = 30
	ELSE IF(InformationAboutDoctors.Experience >= 35 AND InformationAboutDoctors.Experience < 40)
		SET @InterestRate = 35
	ELSE IF(InformationAboutDoctors.Experience >= 40 AND InformationAboutDoctors.Experience < 45)
		SET @InterestRate = 40
	ELSE IF(InformationAboutDoctors.Experience >= 45 AND InformationAboutDoctors.Experience < 50)
		SET @InterestRate = 45
	IF(ProfessionMedicalStaffs.HarmfulConditions == 'Да')
		SET @SurchargeForHazardous = 10
	ELSE IF(ProfessionMedicalStaffs.HarmfulConditions == 'Нет')
		SET @SurchargeForHazardous = 1
	IF (InformationAboutDoctors.MedicalDegree == 'Кандидат')
		SET @AllowanceForTheDegree = 10
	ELSE IF(InformationAboutDoctors.MedicalDegree == 'Доктор')
		SET @AllowanceForTheDegree = 15
	UPDATE InformationAboutDoctors
	SET InformationAboutDoctors.Salary = (ProfessionMedicalStaffs.MinRate + ((ProfessionMedicalStaffs.MinRate * @InterestRate)/100) +
		((ProfessionMedicalStaffs.MinRate * @SurchargeForHazardous)/100) + ((ProfessionMedicalStaffs.MinRate * @AllowanceForTheDegree)/100))
END


Я так понимаю, что это кодобред! Вот и прошу вас помочь решить эту проблему. Просто сроки поджимают, так бы я посидел и подуплил что к чему ((
14 дек 11, 21:43    [11767774]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Я тут немного поковырялся в триггере и поменял его. Надеюсь в лучшею сторону. А также я все расчитываю на вашу помощь...

CREATE TRIGGER UpdSalaryForDoc
ON dbo.InformationAboutDoctors 
AFTER INSERT, UPDATE 
AS
BEGIN
	-- Надбавка за стаж
	DECLARE @InterestRate int
	-- Надбавка за вредные условия
	DECLARE @SurchargeForHazardous int
	-- Надбавка за ученую степень
	DECLARE @AllowanceForTheDegree int
	-- Зарплата
	DECLARE @NewSalary smallmoney
	-- Стаж
	DECLARE @Expir int
	-- Ученая степень
	DECLARE @MedDegree nvarchar(30)
	-- Минимальная ставка
	DECLARE @MinimalRate smallmoney
	-- Вредные условия
	DECLARE @HarmfulCondition nvarchar(3)
	
	SET @Expir = (SELECT Experience FROM InformationAboutDoctors)
	SET @MedDegree = (SELECT MedicalDegree FROM InformationAboutDoctors)
	SET @MinimalRate = (SELECT MinRate FROM ProfessionMedicalStaffs)
	SET @HarmfulCondition = (SELECT HarmfulConditions FROM ProfessionMedicalStaffs)
	
	-- Установка надбавки за стаж
	IF(@Expir > 0 AND @Expir < 5)
		SET @InterestRate = 1
	ELSE IF(@Expir >= 5 AND @Expir < 10)
		SET @InterestRate = 5
	ELSE IF(@Expir >= 10 AND @Expir < 15)
		SET @InterestRate = 10
	ELSE IF(@Expir >= 15 AND @Expir < 20)
		SET @InterestRate = 15
	ELSE IF(@Expir >= 20 AND @Expir < 25)
		SET @InterestRate = 20
	ELSE IF(@Expir >= 25 AND @Expir < 30)
		SET @InterestRate = 25
	ELSE IF(@Expir >= 30 AND @Expir < 35)
		SET @InterestRate = 30
	ELSE IF(@Expir >= 35 AND @Expir < 40)
		SET @InterestRate = 35
	ELSE IF(@Expir >= 40 AND @Expir < 45)
		SET @InterestRate = 40
	ELSE IF(@Expir >= 45 AND @Expir < 50)
		SET @InterestRate = 45
	
	-- Установка надбавка за вредные условия
	IF(@HarmfulCondition = 'Да')
		SET @SurchargeForHazardous = 10
	ELSE IF(@HarmfulCondition = 'Нет')
		SET @SurchargeForHazardous = 1
		
	-- Установка надбавки за степень
	IF(@MedDegree = 'Кандидат')
		SET @AllowanceForTheDegree = 10
	ELSE IF(@HarmfulCondition = 'Доктор')
		SET @AllowanceForTheDegree = 15
		
	-- Пересчет зарплаты
	SET @NewSalary = (@MinimalRate + ((@MinimalRate * @InterestRate)/100) + ((@MinimalRate * @SurchargeForHazardous)/100) + 
	((@MinimalRate * @AllowanceForTheDegree)/100))
	
	-- Обновление зарплаты в таблице
	UPDATE InformationAboutDoctors
	SET Salary = @NewSalary
END
14 дек 11, 23:28    [11768072]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
otets
	-- Обновление зарплаты в таблице
	UPDATE InformationAboutDoctors
	SET Salary = @NewSalary

Это вы всем записям в таблице зарплату менять собрались ?
Наверное правильно менять ее только добавленным\обновленным записям
Почему вы не используете для этого таблицы inserted/deleted ?

otets
-- Установка надбавки за стаж
	IF(@Expir > 0 AND @Expir < 5)
		SET @InterestRate = 1
	ELSE IF(@Expir >= 5 AND @Expir < 10)
		SET @InterestRate = 5
	ELSE IF(@Expir >= 10 AND @Expir < 15)
		SET @InterestRate = 10

Этот "паравоз" нужно поместить в таблицу. С которой делать потом join
14 дек 11, 23:35    [11768089]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2567
otets, для начала
14 дек 11, 23:38    [11768097]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
otets, в вашем случае можно вообще обойтись без триггеров, а сделать все через представление. Но если решите поддерживать Salary триггерами, то:

1. Прочитайте документацию по триггерам -- CREATE TRIGGER (Transact-SQL)
2. Вам нужно два триггера -- и на InformationAboutDoctors и на ProfessionMedicalStaffs
14 дек 11, 23:41    [11768103]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
А вообще у вас спроектировано неверно. Как минимум, нужно иметь два столбца: BaseSalary и Salary, который расчитывается и который хранить не обязательно.
14 дек 11, 23:52    [11768129]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Glory
Это вы всем записям в таблице зарплату менять собрались ?
Наверное правильно менять ее только добавленным\обновленным записям
Почему вы не используете для этого таблицы inserted/deleted ?


В принципе да, изначально я решил обновлять всю таблицу, т.к. Salary в таблице InformationAboutDoctors установлено по умолчанию в 0, т.е. я возлагаю все на плечи триггера, чтобы он сам автоматически пересчитал эту зарплату на основании минимальной ставки и разных надбавок. Если я не в том направлении пошел, то буду рад увидеть как это исправить с помощью inserted/deleted

Glory
Этот "паравоз" нужно поместить в таблицу. С которой делать потом join


А смысл ? Потом снова по аналогичному "паровозу" выбирать значении из поля таблицы. Или я и тут не прав, тогда покажите на пальцах в чем прелесть вашего подхода.


invm
otets, в вашем случае можно вообще обойтись без триггеров, а сделать все через представление. Но если решите поддерживать Salary триггерами, то:

1. Прочитайте документацию по триггерам -- CREATE TRIGGER (Transact-SQL)
2. Вам нужно два триггера -- и на InformationAboutDoctors и на ProfessionMedicalStaffs


В принципе с мсдн я и черпал информацию. Возможно я не правильно понял.
А зачем 2 триггера ? В таблице ProfessionMedicalStaffs хранится статическая информация, которая не пересчитывается, а только участвует в перерасчете зарплаты в таблице InformationAboutDoctors.

invm
А вообще у вас спроектировано неверно. Как минимум, нужно иметь два столбца: BaseSalary и Salary, который расчитывается и который хранить не обязательно.


Так BaseSalary я как бы реализовал через минимальную ставку для каждого из врачебного персонала, и он находится в таблице ProfessionMedicalStaffs поле MinRate. А если делать как вы говорите, то минимальная ставка будет зависеть от конкретного человека, а не от его профессии, а это приведет к лишнему дублированию информации...

Ну и спасибо всем за то, что откликнулись на мою проблему! Буду ждать рекомендаций и помощи...
15 дек 11, 14:50    [11771506]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
otets
В принципе да, изначально я решил обновлять всю таблицу, т.к. Salary в таблице InformationAboutDoctors установлено по умолчанию в 0, т.е. я возлагаю все на плечи триггера, чтобы он сам автоматически пересчитал эту зарплату на основании минимальной ставки и разных надбавок. Если я не в том направлении пошел, то буду рад увидеть как это исправить с помощью inserted/deleted

Т.е. добавили одну новую запись, а у имеющейся уже тысячи записей пересчиталось поле ?

otets
А смысл ? Потом снова по аналогичному "паровозу" выбирать значении из поля таблицы. Или я и тут не прав, тогда покажите на пальцах в чем прелесть вашего подхода.

Смысл как раз делать join, а не "выбирать значении из поля таблицы"

Сообщение было отредактировано: 15 дек 11, 15:05
15 дек 11, 15:04    [11771661]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
otets
Так BaseSalary я как бы реализовал через минимальную ставку для каждого из врачебного персонала, и он находится в таблице ProfessionMedicalStaffs поле MinRate. А если делать как вы говорите, то минимальная ставка будет зависеть от конкретного человека, а не от его профессии, а это приведет к лишнему дублированию информации...
Ну, например, представьте, что завтра вводят вилку по базовым ставкам. Что делать будете?
15 дек 11, 15:57    [11772320]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Glory
Т.е. добавили одну новую запись, а у имеющейся уже тысячи записей пересчиталось поле ?

С этим ясно все! Буду пробовать крутить...

Glory
Смысл как раз делать join, а не "выбирать значении из поля таблицы"

Окей, попробую это позже исправить, мне в первую очередь надо написать хоть как то рабочий триггер, а потом его уже рефакторить.

invm
Ну, например, представьте, что завтра вводят вилку по базовым ставкам. Что делать будете?

Я понял что вы имели ввиду, спасибо за будущие поправки в архитектуре. Но мне пока с этим хоть разобраться. Спасибо за дельный совет!

Вот кое что навоял новенького по триггеру, используя курсор. Курсор проверял в отдельности, он все красиво выполняет и главное так как мне надо, но вот когда я его кручу к триггеру, то тут начинаются проблемы. Триггер нормально компилируется, но потом при вставке данных в таблицу InformationAboutDoctors - пишет что запрос выполняется и все! И так на долго, я лично прождал 15 мин и остановил его. Прошу помощи в корректировке... И ещё по поводу таблиц inserted/deleted - правильно ли я использовал их у себя в трирггере?
CREATE TRIGGER UpdSalaryForDoc
ON dbo.InformationAboutDoctors 
AFTER INSERT, UPDATE 
AS
BEGIN
	SET NOCOUNT ON
	
	-- ID Врача
	DECLARE @ID int
	-- Надбавка за стаж
	DECLARE @InterestRate int
	-- Надбавка за вредные условия
	DECLARE @SurchargeForHazardous int
	-- Надбавка за ученую степень
	DECLARE @AllowanceForTheDegree int
	-- Зарплата
	DECLARE @NewSalary smallmoney
	-- Стаж
	DECLARE @Expir int
	-- Ученая степень
	DECLARE @MedDegree nvarchar(30)
	-- Минимальная ставка
	DECLARE @MinimalRate smallmoney
	-- Вредные условия
	DECLARE @HarmfulCondition nvarchar(3)
	
	DECLARE InformationAboutDoctors_Cursor CURSOR FOR 
	SELECT Inf.InformationAboutDoctorID, Inf.Experience, Inf.MedicalDegree, P.MinRate, P.HarmfulConditions
	FROM inserted AS Inf, ProfessionMedicalStaffs AS P

	OPEN InformationAboutDoctors_Cursor
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM InformationAboutDoctors_Cursor 
		INTO @ID, @Expir, @MedDegree, @MinimalRate, @HarmfulCondition

		WHILE @@FETCH_STATUS <> 0 BREAK
		BEGIN
			-- Установка надбавки за стаж
			IF(@Expir >= 0 AND @Expir < 5) SET @InterestRate = 1
			ELSE IF(@Expir < 10) SET @InterestRate = 5
			ELSE IF(@Expir < 15) SET @InterestRate = 10
			ELSE IF(@Expir < 20) SET @InterestRate = 15
			ELSE IF(@Expir < 25) SET @InterestRate = 20
			ELSE IF(@Expir < 30) SET @InterestRate = 25
			ELSE IF(@Expir < 35) SET @InterestRate = 30
			ELSE IF(@Expir < 40) SET @InterestRate = 35
			ELSE IF(@Expir < 45) SET @InterestRate = 40
			ELSE IF(@Expir < 50) SET @InterestRate = 45
		
			-- Установка надбавка за вредные условия
			IF(@HarmfulCondition = 'Да')
				SET @SurchargeForHazardous = 10
			ELSE IF(@HarmfulCondition = 'Нет')
				SET @SurchargeForHazardous = 1
			
			-- Установка надбавки за степень
			IF(@MedDegree = 'Кандидат')
				SET @AllowanceForTheDegree = 10
			ELSE IF(@MedDegree = 'Доктор')
				SET @AllowanceForTheDegree = 15
			
			-- Пересчет зарплаты
			SET @NewSalary = (@MinimalRate + ((@MinimalRate * @InterestRate)/100) + ((@MinimalRate * @SurchargeForHazardous)/100) + 
			((@MinimalRate * @AllowanceForTheDegree)/100))
			
			-- Обновление зарплаты в таблице
			UPDATE InformationAboutDoctors
			SET InformationAboutDoctors.Salary = @NewSalary
			WHERE @ID = InformationAboutDoctors.InformationAboutDoctorID
		END
	END
	CLOSE InformationAboutDoctors_Cursor
	DEALLOCATE InformationAboutDoctors_Cursor
END	
15 дек 11, 17:35    [11773547]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Упс, забыл кое что изменить в этой версии.
Вместо
WHILE @@FETCH_STATUS <> 0 BREAK

Я использую сейчас
IF @@FETCH_STATUS <> 0 BREAK
15 дек 11, 17:38    [11773587]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
otets
Glory
Т.е. добавили одну новую запись, а у имеющейся уже тысячи записей пересчиталось поле ?

С этим ясно все! Буду пробовать крутить...

Glory
Смысл как раз делать join, а не "выбирать значении из поля таблицы"

Окей, попробую это позже исправить, мне в первую очередь надо написать хоть как то рабочий триггер, а потом его уже рефакторить.

invm
Ну, например, представьте, что завтра вводят вилку по базовым ставкам. Что делать будете?

Я понял что вы имели ввиду, спасибо за будущие поправки в архитектуре. Но мне пока с этим хоть разобраться. Спасибо за дельный совет!

Вот кое что навоял новенького по триггеру, используя курсор. Курсор проверял в отдельности, он все красиво выполняет и главное так как мне надо, но вот когда я его кручу к триггеру, то тут начинаются проблемы. Триггер нормально компилируется, но потом при вставке данных в таблицу InformationAboutDoctors - пишет что запрос выполняется и все! И так на долго, я лично прождал 15 мин и остановил его. Прошу помощи в корректировке... И ещё по поводу таблиц inserted/deleted - правильно ли я использовал их у себя в трирггере?
CREATE TRIGGER UpdSalaryForDoc
ON dbo.InformationAboutDoctors 
AFTER INSERT, UPDATE 
AS
BEGIN
	SET NOCOUNT ON
	
	-- ID Врача
	DECLARE @ID int
	-- Надбавка за стаж
	DECLARE @InterestRate int
	-- Надбавка за вредные условия
	DECLARE @SurchargeForHazardous int
	-- Надбавка за ученую степень
	DECLARE @AllowanceForTheDegree int
	-- Зарплата
	DECLARE @NewSalary smallmoney
	-- Стаж
	DECLARE @Expir int
	-- Ученая степень
	DECLARE @MedDegree nvarchar(30)
	-- Минимальная ставка
	DECLARE @MinimalRate smallmoney
	-- Вредные условия
	DECLARE @HarmfulCondition nvarchar(3)
	
	DECLARE InformationAboutDoctors_Cursor CURSOR FOR 
	SELECT Inf.InformationAboutDoctorID, Inf.Experience, Inf.MedicalDegree, P.MinRate, P.HarmfulConditions
	FROM inserted AS Inf, ProfessionMedicalStaffs AS P

	OPEN InformationAboutDoctors_Cursor
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM InformationAboutDoctors_Cursor 
		INTO @ID, @Expir, @MedDegree, @MinimalRate, @HarmfulCondition

		WHILE @@FETCH_STATUS <> 0 BREAK
		BEGIN
			-- Установка надбавки за стаж
			IF(@Expir >= 0 AND @Expir < 5) SET @InterestRate = 1
			ELSE IF(@Expir < 10) SET @InterestRate = 5
			ELSE IF(@Expir < 15) SET @InterestRate = 10
			ELSE IF(@Expir < 20) SET @InterestRate = 15
			ELSE IF(@Expir < 25) SET @InterestRate = 20
			ELSE IF(@Expir < 30) SET @InterestRate = 25
			ELSE IF(@Expir < 35) SET @InterestRate = 30
			ELSE IF(@Expir < 40) SET @InterestRate = 35
			ELSE IF(@Expir < 45) SET @InterestRate = 40
			ELSE IF(@Expir < 50) SET @InterestRate = 45
		
			-- Установка надбавка за вредные условия
			IF(@HarmfulCondition = 'Да')
				SET @SurchargeForHazardous = 10
			ELSE IF(@HarmfulCondition = 'Нет')
				SET @SurchargeForHazardous = 1
			
			-- Установка надбавки за степень
			IF(@MedDegree = 'Кандидат')
				SET @AllowanceForTheDegree = 10
			ELSE IF(@MedDegree = 'Доктор')
				SET @AllowanceForTheDegree = 15
			
			-- Пересчет зарплаты
			SET @NewSalary = (@MinimalRate + ((@MinimalRate * @InterestRate)/100) + ((@MinimalRate * @SurchargeForHazardous)/100) + 
			((@MinimalRate * @AllowanceForTheDegree)/100))
			
			-- Обновление зарплаты в таблице
			UPDATE InformationAboutDoctors
			SET InformationAboutDoctors.Salary = @NewSalary
			WHERE @ID = InformationAboutDoctors.InformationAboutDoctorID
		END
	END
	CLOSE InformationAboutDoctors_Cursor
	DEALLOCATE InformationAboutDoctors_Cursor
END	
За выделенный фрагмент у нас когда-то (не так уж и давно) вывели бы во двор и расстреляли!
15 дек 11, 17:39    [11773590]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
otets,

совет: работайте с inserted и deleted как с обычными таблицами.
В этом случае локальные переменные и курсоры окажутся просто ненужными.
Обычно в триггерах нет никакой необходимости их применять.

Почитайте про JOIN
И синтаксис UPDATE - в плане использования с FROMом и JOINами с другими таблицами
15 дек 11, 17:43    [11773635]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
Klick
Member

Откуда: Стерлитамак - Москва
Сообщений: 1023
iap
За выделенный фрагмент у нас когда-то (не так уж и давно) вывели бы во двор и расстреляли!


А сейчас расстреливают на месте? :)
Курсоры они такие манящие :)
15 дек 11, 17:46    [11773675]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Klick
iap
За выделенный фрагмент у нас когда-то (не так уж и давно) вывели бы во двор и расстреляли!


А сейчас расстреливают на месте? :)
Курсоры они такие манящие :)
При чём тут курсор?
Ничем неограниченный CROSS JOIN двух таблиц!!!
15 дек 11, 17:50    [11773712]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
Klick
Member

Откуда: Стерлитамак - Москва
Сообщений: 1023
iap
Ничем неограниченный CROSS JOIN двух таблиц!!!


ай ё :) проглядел :) мда :(
напомнило, на прошлой работе, коллега нарисовал хранимы с апдейтами без where ) и оно работало даже почти как надо ) потому что в epr системе триггеры "отчекрыживали" апдейт более чем одной записи ) он еще долго удивлялся матным словам в его адрес
15 дек 11, 18:04    [11773844]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
Нефига я не могу раздуплиться с этим триггером. Каша одна, не получается понять как реализовать то, что вы говорите. Все наверно из-за того, что голова не отдыхает уже 4 день от этого триггера (( Чтоб ему повылазило, а завтра надо демонстрировать результат...

Вот последний вариант того, что я пробовал, но вы наверно скажите, что это все фигня...
CREATE TRIGGER UpdSalaryForDoc
ON dbo.InformationAboutDoctors 
AFTER INSERT, UPDATE 
AS
BEGIN
	SET NOCOUNT ON
	
	-- Надбавка за стаж
	DECLARE @InterestRate int
	-- Надбавка за вредные условия
	DECLARE @SurchargeForHazardous int
	-- Надбавка за ученую степень
	DECLARE @AllowanceForTheDegree int
	-- Зарплата
	DECLARE @NewSalary smallmoney
	-- Стаж
	DECLARE @Expir int
	-- Ученая степень
	DECLARE @MedDegree nvarchar(30)
	-- Минимальная ставка
	DECLARE @MinimalRate smallmoney
	-- Вредные условия
	DECLARE @HarmfulCondition nvarchar(3)
	
	SET @Expir = (SELECT i.Experience FROM inserted i INNER JOIN InformationAboutDoctors inf 
		ON inf.InformationAboutDoctorID = i.InformationAboutDoctorID)
	SET @MedDegree = (SELECT i.MedicalDegree FROM inserted i INNER JOIN InformationAboutDoctors inf 
		ON inf.InformationAboutDoctorID = i.InformationAboutDoctorID)
	SET @MinimalRate = (SELECT MinRate FROM ProfessionMedicalStaffs)
	SET @HarmfulCondition = (SELECT HarmfulConditions FROM ProfessionMedicalStaffs)
	
	-- Установка надбавки за стаж
	IF(@Expir >= 0 AND @Expir < 5) SET @InterestRate = 1
		ELSE IF(@Expir < 10) SET @InterestRate = 5
		ELSE IF(@Expir < 15) SET @InterestRate = 10
		ELSE IF(@Expir < 20) SET @InterestRate = 15
		ELSE IF(@Expir < 25) SET @InterestRate = 20
		ELSE IF(@Expir < 30) SET @InterestRate = 25
		ELSE IF(@Expir < 35) SET @InterestRate = 30
		ELSE IF(@Expir < 40) SET @InterestRate = 35
		ELSE IF(@Expir < 45) SET @InterestRate = 40
		ELSE IF(@Expir < 50) SET @InterestRate = 45
	
	-- Установка надбавка за вредные условия
	IF(@HarmfulCondition = 'Да')
		SET @SurchargeForHazardous = 10
	ELSE IF(@HarmfulCondition = 'Нет')
		SET @SurchargeForHazardous = 1
		
	-- Установка надбавки за степень
	IF(@MedDegree = 'Кандидат')
		SET @AllowanceForTheDegree = 10
	ELSE IF(@MedDegree = 'Доктор')
		SET @AllowanceForTheDegree = 15
	
	-- Пересчет зарплаты
	SET @NewSalary = (@MinimalRate + ((@MinimalRate * @InterestRate)/100) + ((@MinimalRate * @SurchargeForHazardous)/100) + 
	((@MinimalRate * @AllowanceForTheDegree)/100))
	
	-- Обновление зарплаты в таблице
	UPDATE dbo.InformationAboutDoctors
	SET dbo.InformationAboutDoctors.Salary = @NewSalary
	WHERE ProfessionMedicalStaffs.ProfessionMedicalStaffID = InformationAboutDoctors.ProfessionMedicalStaffID
END	


Я был бы очень признателен, если бы вы написали уже в готовом виде долбанный триггер... Мое время на исходе, а голова как выжатый лимон.
15 дек 11, 19:05    [11774210]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
У всех что идеи закончились ?
15 дек 11, 22:53    [11775193]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
И чтож вы так уперлись в этот триггер?
CREATE TABLE dbo.InformationAboutDoctors
(
 InformationAboutDoctorID int IDENTITY NOT NULL PRIMARY KEY,
 Surname nvarchar(30) NOT NULL,
 Name nvarchar(20) NOT NULL,
 Patronymic nvarchar(30) NOT NULL,
 Sex nvarchar(1) NOT NULL,
 Age tinyint NOT NULL,
 MedicalDegree nvarchar(20) NOT NULL,
 Experience tinyint NOT NULL,
 BaseSalary smallmoney NOT NULL DEFAULT 0,
 ProfessionMedicalStaffID int NOT NULL REFERENCES ProfessionMedicalStaffs(ProfessionMedicalStaffID),
 CONSTRAINT InformationAboutDoctorsSex CHECK (Sex IN(N'м',N'ж')),
 CONSTRAINT InformationAboutDoctorsMedicalDegree CHECK (MedicalDegree IN(N'Кандидат',N'Доктор'))
);
go
CREATE TABLE dbo.ProfessionMedicalStaffs
(
 ProfessionMedicalStaffID int IDENTITY NOT NULL PRIMARY KEY,
 Profession nvarchar(30) NOT NULL,
 AdmissionForSurgery nvarchar(3) NOT NULL,
 HarmfulConditions nvarchar(3) NOT NULL,
 MinRate smallmoney NOT NULL,
 CONSTRAINT ProfessionMedicalStaffsProfessions CHECK (Profession IN(N'Хирург',N'Терапевт',N'Невропатолог',N'Окулист',N'Стоматолог',N'Рентгенолог',N'Гинеколог',N'Диетолог',N'Эпидемиолог',N'Психиатр',N'Педиатр')),
 CONSTRAINT ProfessionMedicalStaffsAdmissions CHECK (AdmissionForSurgery IN(N'Да',N'Нет')),
 CONSTRAINT ProfessionMedicalStaffsHarmfulConditions CHECK (HarmfulConditions IN(N'Да',N'Нет'))
);
go
create view dbo.vInformationAboutDoctors
as
select
 i.InformationAboutDoctorID,
 i.Surname,
 i.Name,
 i.Patronymic,
 i.Sex,
 i.Age,
 i.MedicalDegree,
 i.Experience,
 i.BaseSalary,
 i.ProfessionMedicalStaffID,
 cast(i.BaseSalary +
  case
   when i.Experience > 0 and i.Experience < 5 then .01
   when i.Experience >= 5 and i.Experience < 10 then .05
   when i.Experience >= 10 and i.Experience < 15 then .1
   when i.Experience >= 15 and i.Experience < 20 then .15
   when i.Experience >= 20 and i.Experience < 25 then .2
   when i.Experience >= 25 and i.Experience < 30 then .25
   when i.Experience >= 30 and i.Experience < 35 then .3
   when i.Experience >= 35 and i.Experience < 40 then .35
   when i.Experience >= 40 and i.Experience < 45 then .4
   when i.Experience >= 45 and i.Experience < 50 then .45
  end * i.BaseSalary +
  case
   when p.HarmfulConditions = N'Да' then 0.1
   when p.HarmfulConditions = N'Нет' then 0.01
  end * i.BaseSalary +
  case
   when i.MedicalDegree = N'Кандидат' then 0.1
   when i.MedicalDegree = N'Доктор' then 0.15
  end * i.BaseSalary as smallmoney) as Salary
from
 dbo.InformationAboutDoctors i join
 dbo.ProfessionMedicalStaffs p on p.ProfessionMedicalStaffID = i.ProfessionMedicalStaffID;
16 дек 11, 00:10    [11775359]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
otets
Member

Откуда:
Сообщений: 9
invm
И чтож вы так уперлись в этот триггер?
CREATE TABLE dbo.InformationAboutDoctors
(
 InformationAboutDoctorID int IDENTITY NOT NULL PRIMARY KEY,
 Surname nvarchar(30) NOT NULL,
 Name nvarchar(20) NOT NULL,
 Patronymic nvarchar(30) NOT NULL,
 Sex nvarchar(1) NOT NULL,
 Age tinyint NOT NULL,
 MedicalDegree nvarchar(20) NOT NULL,
 Experience tinyint NOT NULL,
 BaseSalary smallmoney NOT NULL DEFAULT 0,
 ProfessionMedicalStaffID int NOT NULL REFERENCES ProfessionMedicalStaffs(ProfessionMedicalStaffID),
 CONSTRAINT InformationAboutDoctorsSex CHECK (Sex IN(N'м',N'ж')),
 CONSTRAINT InformationAboutDoctorsMedicalDegree CHECK (MedicalDegree IN(N'Кандидат',N'Доктор'))
);
go
CREATE TABLE dbo.ProfessionMedicalStaffs
(
 ProfessionMedicalStaffID int IDENTITY NOT NULL PRIMARY KEY,
 Profession nvarchar(30) NOT NULL,
 AdmissionForSurgery nvarchar(3) NOT NULL,
 HarmfulConditions nvarchar(3) NOT NULL,
 MinRate smallmoney NOT NULL,
 CONSTRAINT ProfessionMedicalStaffsProfessions CHECK (Profession IN(N'Хирург',N'Терапевт',N'Невропатолог',N'Окулист',N'Стоматолог',N'Рентгенолог',N'Гинеколог',N'Диетолог',N'Эпидемиолог',N'Психиатр',N'Педиатр')),
 CONSTRAINT ProfessionMedicalStaffsAdmissions CHECK (AdmissionForSurgery IN(N'Да',N'Нет')),
 CONSTRAINT ProfessionMedicalStaffsHarmfulConditions CHECK (HarmfulConditions IN(N'Да',N'Нет'))
);
go
create view dbo.vInformationAboutDoctors
as
select
 i.InformationAboutDoctorID,
 i.Surname,
 i.Name,
 i.Patronymic,
 i.Sex,
 i.Age,
 i.MedicalDegree,
 i.Experience,
 i.BaseSalary,
 i.ProfessionMedicalStaffID,
 cast(i.BaseSalary +
  case
   when i.Experience > 0 and i.Experience < 5 then .01
   when i.Experience >= 5 and i.Experience < 10 then .05
   when i.Experience >= 10 and i.Experience < 15 then .1
   when i.Experience >= 15 and i.Experience < 20 then .15
   when i.Experience >= 20 and i.Experience < 25 then .2
   when i.Experience >= 25 and i.Experience < 30 then .25
   when i.Experience >= 30 and i.Experience < 35 then .3
   when i.Experience >= 35 and i.Experience < 40 then .35
   when i.Experience >= 40 and i.Experience < 45 then .4
   when i.Experience >= 45 and i.Experience < 50 then .45
  end * i.BaseSalary +
  case
   when p.HarmfulConditions = N'Да' then 0.1
   when p.HarmfulConditions = N'Нет' then 0.01
  end * i.BaseSalary +
  case
   when i.MedicalDegree = N'Кандидат' then 0.1
   when i.MedicalDegree = N'Доктор' then 0.15
  end * i.BaseSalary as smallmoney) as Salary
from
 dbo.InformationAboutDoctors i join
 dbo.ProfessionMedicalStaffs p on p.ProfessionMedicalStaffID = i.ProfessionMedicalStaffID;


ОГРОМНОЕ СПАСИБО! Я ДАЖЕ НЕ МОГУ ВЫРАЗИТЬ ТО, КАК ВЫ МЕНЯ ВЫРУЧИЛИ!!!
И в правду так намного понятнее и красивее...Если честно, то я бы и до такого не додумался ((
И можно последний маленький вопросик, что означает буква N ?
N'Кандидат'
16 дек 11, 01:14    [11775479]     Ответить | Цитировать Сообщить модератору
 Re: Автовычисляемое поле таблицы  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
otets
И можно последний маленький вопросик, что означает буква N ?

Что означает N перед строковой константой ?
16 дек 11, 07:59    [11775665]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить