Easy trigger? 3

[nevergiveup01]

Hello, I'm trying to create a trigger that will copy the CustID from the customer table to the tblServiceQuote table whenever a new record is added to the customer table.. I'm getting the error message: the parameter is incorrect.. What am I doing wrong? See below..





ALTER TRIGGER customer
ON tblcustomer
FOR insert
AS
BEGIN
INSERT INTO tblServiceQuote (CustID)
Select CustID
from inserted

END

 

[ESquared]

I just have to weigh in...

Please note that I am talking here about things like audits and parent-child relationships, not about implementing super-complex conditional business logic.

I created a stored procedure that adds audit triggers to any tables I wish. If a data type is changed or a column is added or removed, one has but to run the SP and all the triggers are updated to handle the change.

This wasn't something designed as an afterthought, it isn't magical—it took lots of thought and effort, and it's very solid and is used in my production databases. It works well and every single thing that I wish to be audited is audited no matter where the update comes from. Changes to primary keys and incompatible data type changes require special handling (just some DDL tweaks on the history table is all), but they would require that and a whole lot more with SPs, anyway (the DDL on the history table plus modifying the SP, too).

If I happen to forget something, it also has the benefit of in most cases failing when I run the audit-trigger-creating SP instead of failing at data insertion time. Early and conspicuous failure is a *great* feature of systems—the alternatives are late (user-presented instead of developer-presented) failure or early and silent failure (no one knows about it until things are really munged). If you're using SPs and forget to update one, or make a mistake in implementing the change, you won't know until you insert some data.

Also, since my trigger-creating code is in one SP, I can thoroughly and completely test it, and then know that it will consistently and correctly function for every table I want. If I have fifty tables today and tomorrow add fifty more tables, I can have full auditing in seconds, rather than having to write separate auditing code in each of 50 SPs, each of which I could make a mistake in and miss. Or I could fully test the auditing portion of 50 new SPs. One rule of software development is that quantity of bugs directly correlates to quantity of code written. By leveraging existing code I reduce the amount of code I'm writing and end up making far fewer mistakes.

So, moving on... my experience tells me that you guys are being dangerously optimistic that no one will ever gain access to your database and perform a direct insert/update/delete to the tables that are governed by your stored procedures. It only has to happen once to cause serious business problems. How do you know that you will be at the company in 5 years to keep the policy enforced that you envisioned? How do you know that you won't be hit by something you didn't expect? For example, people with the domain right "local administrator" are by default administrators on SQL Server 2000 boxes. So changes that have nothing to do with your database could affect security on it.

Every argument you guys make for using SPs instead of triggers should be compared to a similar argument for using SPs instead of foreign key constraints. What I'm saying is that I'm open to the possibility of cases where SPs are superior to triggers, just like I'm open to the possibility of cases where SPs are superio to FK constraints: rarely.

I hope you would agree with me that calling FK constraints "bolted on as an afterthought", "automagical", or in any other way disparaging them as being an example of poor programming practice would be just plain silly. There's no objective support for triggers being inherently bad just because it happens "behind the scenes" like FK constraints do.

I recognize you probably care nothing for convincing me, but to make a case that *would* convince me to use SPs when triggers will do the job more reliably, you would have to have an argument that would be just as compelling as one that argued to not use FK constraints, instead enforcing that policy in SPs.