MySql Trigger that inserts a record in another table if id isn't a duplicate

Question

I have seen similar questions to mine, however nothing seems to fit the scenario exactly.

I have 2 tables, bills and accounts.

If a bill is pushed into the bills table and there is not already an account, I want to create a record in the accounts table.

I have:

 ...
    BEFORE INSERT ON bills
    FOR EACH ROW 
    BEGIN
        INSERT INTO accounts (id, field1, field2, field3, field4)
        SELECT accountID, 'value1', value2, value3, value4
        FROM bills
        WHERE ((SELECT LEFT(accountID, 5) = 'XXXXX' |
        (SELECT LEFT(accountID, 5) = 'XXXXX' |
        (SELECT LEFT(accountID, 5) = 'XXXXX' |
        (SELECT LEFT(accountID, 5) = 'XXXXX' |
        (SELECT LEFT(accountID, 5) = 'XXXXX')) &&
        accountID NOT IN (SELECT id from accounts); ## I know this line is not right
    END$$

From What I have read elsewhere on SO my options seem to be ON DUPLICATE KEY UPDATE or INSERT IGNORE, neither of which I can seem to make work, nor can I find an example with INSERT, SELECT, FROM and WHERE all involved.

What am I missing?

EDIT: I can make it insert the record, but depending on how I have tried it I always get either "duplicate id" error, or it doesnt insert the record.

Please add table structure and example data.

juergen d
– juergen d

2013-04-05 16:55:51 +00:00
Commented Apr 5, 2013 at 16:55 — juergen d
– juergen d, Commented Apr 5, 2013 at 16:55

somnath · Accepted Answer · 2013-04-05 18:14:41Z

1

Havent coded in MySQL for quite a while hence please excuse if there is some small syntax error. However, you will get the idea what needs to be done in the following code:

before insert on bills
for each row
begin
  declare @li_cnt int;
  select count('') into @li_cnt from accounts where accounts.id_account = inserted.id_account;
  if @li_cnt = 0 then
    insert into accounts (id_account) values (inserted.id_account)
  end

end

However, this type of issue should be handled at the middle tier application server level and not at the database trigger level where one should be actually implementing strict policies and not complying to business requirements. So in that respect, assuming your app to be a PHP web app, you should have the bill's class method save() which should be checking the same and inserting the value in accounts table using the accounts table's class and then inserting the row in the bills table. Using this method one can really make modern sophisticated logic e.g. you can restrict insertion of bills for a specific account if the accounts class method that you call from the bill insertion sends back a decline response. Although it seems you require a simple insertion of data into accounts table when a bill is inserted with an accounts code / ID that doesnt exist in the master accounts table, I still feel coding the right way even in simple requirements makes for a good platform for an app to later scale and become pretty complicated with minimum issues maintenance cost.

answered Apr 5, 2013 at 18:14

somnath

1,3351 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deez Over a year ago

Unfortunately, in my case I am not in control of the data, and it is being pushed to the MySql database by powers I cannot control. The database is not populated through my app. My app only displays the data. I realize this is not an ideal solution. But thanks, I will give it a shot and report back.

Collectives™ on Stack Overflow

MySql Trigger that inserts a record in another table if id isn't a duplicate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related