Insert and After Insert Trigger

[Safwan]

Hi

BDS2006:BCB
IB7.5 SP1

Create Table A (A Integer Not Null Primary Key);

Create Table B (B Integer);
Insert Into B (B) Values (1);
Commit;

Create Trigger A_AI FOR A
Active After Insert
As
Begin
Update B Set B = B + New.A;
End;

That's in the Database.

IBDataSet1->Insert ();
IBDataSet1B->AsInteger = 1000;
IBDataSet1->Post ();
....
IBDataSet1->Edit ();
IBDataSet1B->AsInteger = 500;
IBDataSet1->Post ();

IBTransaction1->Commit ();

I suppose to got the value 501 in the table A; but I got 1001.
Does the trigger get the first value inserted and ignore any changing of the
value later on.

Thanks.

[Safwan]

Yes it is my fault

change all the IBDataSet1B with IBDataSet1A

IBDataSet1->Insert ();
IBDataSet1A->AsInteger = 1000;
IBDataSet1->Post ();
IBDataSet1->Edit ();
IBDataSet1A->AsInteger = 500;
IBDataSet1->Post ();

Post by Safwan
Create Table A (A Integer Not Null Primary Key);
Create Table B (B Integer);
Insert Into B (B) Values (1);
Commit;

One record in B with value 1.

Post by Safwan
Create Trigger A_AI FOR A
Active After Insert
As
Begin
Update B Set B = B + New.A;
End;
IBDataSet1->Insert ();
IBDataSet1B->AsInteger = 1000;
IBDataSet1->Post ();

Adds a *second* record in B with a value of 1000.

Post by Safwan
IBDataSet1->Edit ();
IBDataSet1B->AsInteger = 500;
IBDataSet1->Post ();

If still on the newly inserted record, then 1000 has now changed to 500.

Post by Safwan
I suppose to got the value 501 in the table A; but I got 1001.

If you have 1001 then your sample code above is not what you actually
executed, you don't show any statements against table A which is where the
trigger is, so I cannot guess what is happening. Show the *actual* code
you executed.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"A man is likely to mind his own business when it is worth minding,
when it is not, he takes his mind off his own meaningless affairs by
minding other people's business." - Eric Hoffer

[Safwan]

Create Table A (A Integer Not Null Primary Key);

Create Table B (B Integer);
Insert Into B (B) Values (1);
Commit;

Create Trigger A_AI FOR A
Active After Insert
As
Begin
Update B Set B = B + New.A;
End;

// IBDataSet1 = Table A

IBDataSet1->Insert ();
IBDataSet1A->AsInteger = 1000;
IBDataSet1->Post ();

IBDataSet1->Edit ();
IBDataSet1A->AsInteger = 500;
IBDataSet1->Post ();

If still on the newly inserted record, then 1000 has now changed to 500.

and as you say "I'am still on the newly inserted record".
And then 1000 has now changed to 500. (in the same record - the new one).

I think that the trigger will executed with the new value (500) but is is
executed with the value (1000).

so, I suppose to got the value 501 in the table A; but I got 1001.

---
I'am sory for the typing error in the previous message

[Wayne Niddery [TeamB]]

Post by Safwan
Create Trigger A_AI FOR A
Active After Insert
As
Begin
Update B Set B = B + New.A;
End;
I think that the trigger will executed with the new value (500) but
is is executed with the value (1000).
so, I suppose to got the value 501 in the table A; but I got 1001.

Your trigger is specifed for *insert*, and so only fires for inserts, not
edits (updates). So 1001 is correct. If you also want B to be updated on
*edits* to A, then you need a second trigger that is active after update.

[Safwan]

Post by Wayne Niddery [TeamB]
Your trigger is specifed for *insert*, and so only fires for inserts, not
edits (updates). So 1001 is correct. If you also want B to be updated on
*edits* to A, then you need a second trigger that is active after update.

Thanks.

I use a DBGrid to let the user to insert the records in table A. and when
the user insert the 1000 and then change it to 500. the user think that he
insert a new record with 500 not 1000. but the database (trigger) think that
the 1000 is inserted and the 500 is update.
this will be confused.

and if I make a secord trigger that is active after update so it will change
the value to 1500.

Any way; thank you to your time.
I will think in another way to solve it.

[Wayne Niddery [TeamB]]

Post by Safwan
and if I make a secord trigger that is active after update so it will
change the value to 1500.

Yes, that's right. Perhaps you could "backup" a bit and explain what you
really need to do - what the user expects and what Table B in your example
is supposed to represent or track. Maybe we can suggest ideas for you.

[Safwan]

Post by Wayne Niddery [TeamB]
Yes, that's right. Perhaps you could "backup" a bit and explain what you
really need to do - what the user expects and what Table B in your example
is supposed to represent or track. Maybe we can suggest ideas for you.

Thank you for you concern.

What I want to do is like this:

I have a table for Invoices

InvID Name Value Payments
1 Someone 500 0
2 Someone 1000 0
3 Someone 350 150

Another table for payments

ID Value InvID
1 150 3

So, I make a trigger on the second table which is for payments, when the
user insert a record the trigger take the value and add it to the Payments
field in the Invoices table.

I use the DBGrid for the payments table, but as I show you on the previous
messages.
When the user add a record and update it, and then commit, the first value
he entered is go to Payments feild.

Any way, I solve it by a store procedure.
But if you have any idea, I will be glad.

Thanks you and thank TeamB for thier fatigue.

[Wayne Niddery [TeamB]]

Post by Safwan
I have a table for Invoices
InvID Name Value Payments
1 Someone 500 0
2 Someone 1000 0
3 Someone 350 150
Another table for payments
ID Value InvID
1 150 3
So, I make a trigger on the second table which is for payments, when
the user insert a record the trigger take the value and add it to the
Payments field in the Invoices table.

Ok. Then the insert trigger is fine, simply add New.Value.

For the update trigger, it needs to be (for the correct invoice record)

Invoice.Payments = Invoice.Payments - Old.Value + New.Value;

[Wayne Niddery [TeamB]]

Post by Safwan
Create Table A (A Integer Not Null Primary Key);
Create Table B (B Integer);
Insert Into B (B) Values (1);
Commit;

One record in B with value 1.

Post by Safwan
Create Trigger A_AI FOR A
Active After Insert
As
Begin
Update B Set B = B + New.A;
End;
IBDataSet1->Insert ();
IBDataSet1B->AsInteger = 1000;
IBDataSet1->Post ();

Adds a *second* record in B with a value of 1000.

Post by Safwan
IBDataSet1->Edit ();
IBDataSet1B->AsInteger = 500;
IBDataSet1->Post ();

If still on the newly inserted record, then 1000 has now changed to 500.

Post by Safwan
I suppose to got the value 501 in the table A; but I got 1001.

If you have 1001 then your sample code above is not what you actually
executed, you don't show any statements against table A which is where the
trigger is, so I cannot guess what is happening. Show the *actual* code you
executed.