ReSQL.ru
     
powered by simpleCommunicator - 2.0.38     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Создание триггера, не позволяющего добавлять в таблицу одинковые записи
8 сообщений из 8, страница 1 из 1
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38183852
hmmm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hmmm
Участник
Коллеги, всем привет!

Встала у меня задача написать такой триггер, который бы при вставке новых записей в таблицу проверял есть ли в таблице уже такая запись и если нет, то добавлял ее в таблицу.

Таблица следующая:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE  TABLE IF NOT EXISTS `data1` (
  `id_data` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `coach` TEXT COLLATE 'NOCASE',
  `date` TEXT COLLATE 'NOCASE',
  `status` TEXT COLLATE 'NOCASE',
  `customer` TEXT COLLATE 'NOCASE',
  `fio` TEXT COLLATE 'NOCASE',
  `stock` TEXT COLLATE 'NOCASE',
  `cost` TEXT COLLATE 'NOCASE',
  `condition_payment` TEXT COLLATE 'NOCASE')



При вставке новых значений уникальность записи в таблице можно выявить составным ключем по полям date и fio .
Начинаю писать триггер:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TRIGGER IF NOT EXISTS update_data INSTEAD OF INSERT ON data1
BEGIN
	UPDATE data1 SET
			coach = new.coach,
			date = new.date,
			status = new.status,
			customer = new.customer,
			fio = new.fio,
			stock = new.stock,
			cost = new.cost,
			condition_payment = new.condition_payment
		WHERE
			date = new.date AND
			fio = new.fio;				
END;



Выскакивают ошибки:
Ошибка в запросe (1): cannot create INSTEAD OF trigger on table: main.data1
Ошибка в запросe (1): cannot commit - no transaction is active


Если в триггере поменять INSTEAD OF на BEFORE , то выскакивают такие ошибки:
Ошибка в запросe (1): near "fio": syntax error
Ошибка в запросe (1): cannot commit - no transaction is active

Если переписать триггер так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TRIGGER IF NOT EXISTS update_data BEFORE INSERT ON data1
FOR EACH ROW WHEN NEW.date = OLD.date AND NEW.fio = OLD.fio
BEGIN
	UPDATE data1 SET
			coach = NEW.coach,
			date = NEW.date,
			status = NEW.status,
			customer = NEW.customer,
			fio = NEW.fio,
			stock = NEW.stock,
			cost = NEW.cost,
			condition_payment = NEW.condition_payment;			
END;


Выскакивают опять ошибки:
Ошибка в запросe (1): near "condition_payment": syntax error
Ошибка в запросe (1): cannot commit - no transaction is active


Никак не пойму, почему идет ругань на последний параметр ...
Ругается даже на такое:
Код: sql
1.
2.
3.
4.
5.
CREATE TRIGGER update_data BEFORE INSERT ON data1 
WHEN EXISTS (SELECT * FROM data1 WHERE date = NEW.date AND fio = NEW.fio)
BEGIN 
	RAISE(ABORT,1);
END;


Пишет:
Ошибка в запросe (1): near "RAISE": syntax error


Как буд-то команда между BEGIN и END неправильно заканчивается, хотя везде после них стоит точка с запятой.
Подскажите, что я делаю неправильно?
...
Рейтинг: 0 / 0
14.03.2013, 14:07
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38183995
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
hmmm,

а зачем триггер?Если при нахождении записи ее нужно заменять то достаточно CONSTRAINT

Код: sql
1.
2.
3.
4.
CREATE TABLE 
....
[condition_payment] TEXT COLLATE 'NOCASE',
CONSTRAINT [cn_fio_date] UNIQUE([date], [fio]) ON CONFLICT REPLACE)
...
Рейтинг: 0 / 0
14.03.2013, 15:12
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38184017
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
насколько я помню, хотя могу и ошибаться, INSTEAD OF используется для вьюх
...
Рейтинг: 0 / 0
14.03.2013, 15:19
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38184070
hmmm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hmmm
Участник
Спасибо на наводку! Что-то мне в голову уперлось триггером это сделать и все тут. Про другие способы даже не подумал.

Но вот почему ошибки у меня выскакивают при добавлении триггеров мне всеравно не понятно ... Хотелось бы разобраться в чем я не прав.
...
Рейтинг: 0 / 0
14.03.2013, 15:39
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38184157
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
hmmm,

по первому я уже писал насчет INSTEAD OF и таблиц

по второму в insert триггерах нельзя использовать OLD, только NEW

по RAISE - использовать

Код: sql
1.
SELECT RAISE(ABORT,'1');
...
Рейтинг: 0 / 0
14.03.2013, 16:12
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38185335
hmmm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hmmm
Участник
pit_alex,

Спасибо! Что-то я не подумал, что при INSERT триггере значению OLD неоткуда взяться. С RAISE тоже понятно все.
Но у меня осталось маленькое такое неудовлетворение в том, как работает
Код: sql
1.
CONSTRAINT [cn_fio_date] UNIQUE([date], [fio]) ON CONFLICT REPLACE


Если так написать при создании таблицы, то в случае добавления новых строк с такими же датами и ФИО происходит обновление существующей в таблице записи + меняет значение первичного ключа id_data , что мне бы не хотелось. В принципе, для данной таблицы такое поведение терпимо, но только до тех пор, пока я не начну заниматься нормализацией данных.

Например, решу я сделать справочник по клиентам
Код: sql
1.
2.
3.
4.
5.
6.
CREATE  TABLE IF NOT EXISTS clients (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  client_name TEXT COLLATE 'NOCASE',
  email TEXT COLLATE 'NOCASE',
  company_name TEXT COLLATE 'NOCASE',
  CONSTRAINT [cn_cl_nm] UNIQUE(client_name, email) ON CONFLICT REPLACE)


и в нормализованной таблице data2 буду в поле client хранить id клиента из таблицы clients .
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE  TABLE IF NOT EXISTS data2 (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  coach INTEGER,
  date INTEGER,
  status INTEGER,
  client INTEGER,
  stock INTEGER,
  cost TEXT COLLATE 'NOCASE',
  condition_payment TEXT COLLATE 'NOCASE',
  CONSTRAINT [cn_cl_dt] UNIQUE(client, date) ON CONFLICT REPLACE)


То в случае, если мне в таблицу clients попробуют добавить запись с значенниями client_name и email совпадающими с одной из уже имеющихся записей, то у меня перезапишется первичный ключ id . В этом случае, мне необходимо каким-то образом поменять в таблице data2 значение поля client на то, которое будет соответствовать новому id из таблицы clients . Для этого необходимо написать триггер, который бы срабатывал на изменение id и обновлял в таблице data2 значения соответствующих полей client .
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DELIMITER ;;
CREATE TRIGGER clients_ai AFTER INSERT ON clients FOR EACH ROW
BEGIN
UPDATE data2 SET
			id = id,
			coach = coach,
			date = date,
			status = status,
			-- Здесь мы обновляем идентификатор клиента на новый
			client = NEW.id,
			stock = stock,
			cost = cost,
			condition_payment = condition_payment
WHERE
-- Здесь очень хочется написать вместо id - OLD.id, но как это правильно сделать я пока не понял, т.к. не знаю откуда взять старый идентификатор клиента :(
client = id;
END;;
DELIMITER ;


В общем, нужно логику триггера как-то иначе делать. Как -- пока не знаю. Надеюсь на подсказки и идеи!

Использовал следующие тестовые данные:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
INSERT INTO clients (client_name, email, company_name) VALUES ('Vasya Pupkin', 'vasya@pupkin.ru', 'Vasya&Co');
INSERT INTO clients (client_name, email, company_name) VALUES ('Mike Doe', 'mike@doe.com', 'Mike&Co');
INSERT INTO clients (client_name, email, company_name) VALUES ('John Smith', 'john@smith.com', 'John&Co');
INSERT INTO clients (client_name, email, company_name) VALUES ('Petya Petrov', 'petya@petrov.ru', 'Petya&Co');
INSERT INTO clients (client_name, email, company_name) VALUES ('Oleg Tabakov', 'oleg@tabakov.ru', 'Oleg&Co');

INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (1, 2, 3, 1, 1, '3000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (2, 3, 4, 2, 2, '4000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (3, 4, 5, 3, 1, '5000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (4, 5, 6, 4, 2, '6000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (5, 6, 7, 5, 1, '7000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (6, 7, 8, 1, 2, '8000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (7, 8, 9, 2, 1, '9000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (8, 9, 10, 3, 2, '10000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (9, 10, 11, 4, 1, '11000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (10, 11, 12, 5, 2, '12000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (11, 12, 13, 1, 1, '13000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (12, 13, 14, 2, 2, '14000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (13, 14, 15, 3, 1, '15000', 'Все оплачено');
...
Рейтинг: 0 / 0
15.03.2013, 11:56
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38185464
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
hmmm,

много текста неасилил :-)

а вообще, при нарушении уникальности можно генерить исключение и обрабатывать на стороне клиента, или все таки обновлять запись в триггере, и потом рейзить тихую ошибку, типа такого

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TRIGGER update_data1 BEFORE INSERT ON data1 
WHEN EXISTS (SELECT 1 FROM data1 WHERE date = NEW.date AND fio = NEW.fio)
BEGIN 
UPDATE data1 SET
			coach = new.coach,
			status = new.status,
			customer = new.customer,
			stock = new.stock,
			cost = new.cost,
			condition_payment = new.condition_payment
		WHERE
			date = new.date AND
			fio = new.fio;
SELECT RAISE(IGNORE);
END;
...
Рейтинг: 0 / 0
15.03.2013, 12:51
| Ответить | Цитировать | Написать  
Создание триггера, не позволяющего добавлять в таблицу одинковые записи
    #38186602
hmmm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hmmm
Участник
pit_alex,

Спасибо!

Создаю таблицу:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE  TABLE IF NOT EXISTS data2 (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  coach INTEGER,
  date INTEGER,
  status INTEGER,
  client INTEGER,
  stock INTEGER,
  cost TEXT COLLATE 'NOCASE',
  condition_payment TEXT COLLATE 'NOCASE');


И триггер к ней:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
DELIMITER ;;
CREATE TRIGGER update_data2 BEFORE INSERT ON data2 
WHEN EXISTS (SELECT * FROM data2 WHERE date = NEW.date AND client = NEW.client)
BEGIN 
  UPDATE data2 SET
    coach = new.coach,
    status = new.status,
    stock = new.stock,
    cost = new.cost,
    condition_payment = new.condition_payment
  WHERE
    date = new.date AND
    client = NEW.client;
  SELECT RAISE(IGNORE);
END;;
DELIMITER ;


Начинаю заносить данные:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (1, 2, 3, 1, 1, '3000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (2, 3, 4, 2, 2, '4000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (3, 4, 5, 3, 1, '5000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (4, 5, 6, 4, 2, '6000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (5, 6, 7, 5, 1, '7000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (6, 7, 8, 1, 2, '8000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (7, 8, 9, 2, 1, '9000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (8, 9, 10, 3, 2, '10000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (9, 10, 11, 4, 1, '11000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (10, 11, 12, 5, 2, '12000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (11, 12, 13, 1, 1, '13000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (12, 13, 14, 2, 2, '14000', 'Все оплачено');
INSERT INTO data2 (coach, date, status, client, stock, cost, condition_payment) VALUES (13, 14, 15, 3, 1, '15000', 'Все оплачено');


В результате получается супер-таблица!
idcoachdatestatusclientstockcostcondition_payment1123113000Все оплачено2234224000Все оплачено3345315000Все оплачено4456426000Все оплачено5567517000Все оплачено6678128000Все оплачено7789219000Все оплачено889103210000Все оплачено9910114111000Все оплачено101011125212000Все оплачено111112131113000Все оплачено121213142214000Все оплачено131314153115000Все оплачено

При создании таблицы я не использовал:
Код: sql
1.
CONSTRAINT [cn_cl_dt] UNIQUE(client, date) ON CONFLICT REPLACE


Если мне надо сделать так, чтобы при каждой вставке строки я смотрел не существует ли у меня уже такая и если существует, то ничего добавлять не надо, тогда мой триггер должен выглядеть следующим образом?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
DELIMITER ;;
CREATE TRIGGER dnt_duplicate_data2 BEFORE INSERT ON data2 
WHEN EXISTS (SELECT * FROM data2 WHERE 
                                   coach = new.coach AND 
                                   date = NEW.date AND 
                                   status = new.status AND 
                                   client = NEW.client AND 
                                   stock = new.stock AND 
                                   cost = new.cost AND 
                                   condition_payment = new.condition_payment)
BEGIN
  SELECT RAISE(IGNORE);
END;;
DELIMITER ;
...
Рейтинг: 0 / 0
16.03.2013, 12:58
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Создание триггера, не позволяющего добавлять в таблицу одинковые записи
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=38185335&tid=2008924]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 275ms
total:  413ms
созд. / загр.: 413ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]