I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
(There are other fields, and the primary key is a combination of 5 columns)
For one code ('C0') I want to implement a rule that if I attempt to insert a
combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are
already in the table. (for non-'C0' codes, this rule doesn't apply.)
select * from assets;
Code Acct1 Acct2
'C0' 'name1' 'name2'
insert into assets values('C0', 'name1', 'name3'); -- ignore
insert into assets values('C0', 'name3', 'name4'); -- succeed
insert into assets values('C0', 'name1', 'name2'); -- ignore
insert into assets values('C1', 'name1', 'name2'); -- succeed
I tried:
insert into assets select 'C0', 'name1', 'name3' where not exists (select 1
from assets where Code='C0' and acct1='name1');
but it went ahead and inserted the row anyway. My hope was that since the where
clause was false, the select would return nothing and thus nothing would be
inserted.
Is there a way I can craft the insert to do this, or do I need to set up a
trigger to ignore the insert? If the latter, how should the trigger read?
Thanks,
David
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
