I am just back in the field after 5 years and am very rustly so please be patient. I am working on sql server express and I need to capture new/updated records(only parts of them actually) from a single table in order to insert into another table. (I will be using this other table as my data source for a crystal report.) I have no test environment and will have to build and populate around 6 tables to test the trigger. Is a trigger my only option? Thank you for any input.
+3
A:
First, get a test environment. You can't work without one safely.
You can use triggers for this yes, but do you really need a separate table or will a query or view (not sure if you can have a view in Express) do as the source of the report instead.
If you choose to use a trigger, make sure to test multpe row inserts/updates/deletes. Triggers need to be designed to act on sets of data not one row at a time.
HLGEM
2009-12-10 22:30:23
Holy moly! I had totally forgotten that you can do that in crystal reports. Thank you soooo much! You saved me like a weeks worth of work. I would kiss you if i could. Thx again :D
2009-12-11 18:27:58
+4
A:
If you have 2005/2008, you can also use OUTPUT as in this example -- providing you control the inserts.
Damir Sudarevic
2009-12-10 22:30:32
OUTPUT is the best option, because triggers are not as obvious. There are things you can't do in OUTPUT, but it was designed for exactly the type of auditing the OP is trying to do.
womp
2009-12-10 22:38:00
A:
Triggers are like Regular Expressions, if you have a problem and you solve it with either one, now you have 2 problems. I would just do to inserts in to the separate tables a single transaction from your client code. Mainly because side effect programming like Triggers do is just asking for problems down the road. If you accidentally insert some bad data and the triggers fire you can't undo the actions of the triggers easily. Triggers should be a solution or LAST resort. Inserts are no longer orthogonal, this leads to really hard to maintain code. There are a lot of better design decisions, like someone else mentioned Views, and Materialized Views are a better solution if your database supports them.
fuzzy lollipop
2009-12-10 22:48:46
Triggers are not hard to maintain if you have a competent dba. And they are the best solution for anything that involves data integrity which can't be handled through constraints or unique indexes. Data integrity cannot be maintained from the application as it is not the only thing that changes data in a database. To not use triggers because you think they are hard is short-sighted at best.
HLGEM
2009-12-11 14:01:54
tiggers are not "hard" they are buggy and are an anti-pattern of side effect programming. Like I said they are not Orthogonal and can be non-deterministic. Avoiding triggers is good advice.
fuzzy lollipop
2009-12-29 18:06:29