February 1, 2007 at 5:45 pm
I have a sql table named "Billing_Detail" with the following columns:
Billing_ID, Code, Code_ID
I've set up a separate user interface in MS Access where the user can only enter the Invoice_Billing_ID and Code. I want to put a trigger on the Billing_Detail table so that every time the user enters a new code or if a code is modified, the trigger will fire and update the code_id (the code_id is stored in a table called "Codes").
Any help would be much appreciated. Please let me know if I need to provide more information since I am quite new to this.
February 1, 2007 at 7:22 pm
I'm not even sure that the table design is correct but that is for another discussion...
You can send the code_id in the access for and hide it from the users. That way there won't be no need for the update trigger.
PS since you'll most likely need a joing to get this information you'll need to lookup the unique table form property in access to get the form updating correctly (at all actually).
February 2, 2007 at 7:26 am
I appreciate the response but my example was more for help creating this type of trigger. If you were to create a trigger based on my example, how would you set it up?
February 2, 2007 at 7:38 am
I need the tables definition to know how to link the code to its code id. I can't answer your particular question correctly on the first try without that. Any answers at this point are pure speculation (doesn't mean they would be wrong).
February 2, 2007 at 8:29 am
See the two tables below. The user enters a code that gets populated in the BILLING_DETAIL table. That code will correspond to a code in the CODES table. So every time a user enters a code, I want the trigger to grab the CODE_ID from the CODES table and populate it in the CODE_ID column of the BILLING_DETAIL table.
| BILLING DETAIL TABLE | ||
| INVOICE_BILLING_ID | int | 4 |
| ITEM_NUMBER | tinyint | 1 |
| CODE | varchar | 11 |
| CODE_ID | int | 4 |
| ITEM_DESCRIPTION_ID | smallint | 2 |
| RATE | money | 8 |
| CODES TABLE | ||
| CODE_ID | int | 4 |
| CODE_TYPE | tinyint | 1 |
| CODE | varchar | 11 |
February 2, 2007 at 1:41 pm
Why do you want to store the code and the code ID in the billing detail table? It's poor table design. (Unless this is some form of data warehouse and you're denormalising for performance reasons)
In Access, you can create a drop down based on the codes table, with the id as the bound field and the code as the display field. Then the user selects the code from the drop down, but it's the ID that gets written back to the table.
If this is for theory....
Note this is the insert trigger. You'll need one on update also, in case the code is changed. I'm also ignoring the possibility that the user mistypes the code and the text they've entered doesn't match anything in Codes.
I also haven't tested this
CREATE TRIGGER trg_UpdateCodesTable ON BillingDetails FOR INSERT
AS
UPDATE BillingDetails SET CodeID = c.CodeID
FROM inserted i INNER JOIN Codes c on i.Code = c.Code
WHERE i.Invoice_Billing_ID = BillingDetails.Invoice_Billing_ID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply