Is having a trigger that inserts a row in Table A, when a row in same table is inserted by

I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.

Thanks

Hey,

I would prefer stored procedure; the reason is I have no control over triggers, so it makes it easier from a maintenance perspective on me, which is what I think you may also be under that restraint? I don't know how much slowing down it would do; I would think a lot of that depends on volume of data as well.

If you are inserting the main data using a stored procedure, then that stored procedure can do two inserts. You can also group them in a transaction so if one fails, both statements are rolled back for safety purposes.

Microsoft includes a lot of things: they include xp_cmdshell which most DBA's disable because of the power of it. So they will include things even though they may not be the best to implement...

|||

Actually, your DBA is correct. I always recommend, if possible, opting for stored procedures as opposed to triggers. The trigger will save you time if you are inserting rows into the table from many different sources in many different ways. But, if the only point of entry is a stored procedure, there's no reason not to place it in the sp. The farther back in SQL Server version you go, the less reliable triggers seem to get - and, yes, they can place locks on tables. Microsoft included them because they have their place - but it is a mistake to use them as substitutes for logical flow.

This shouldn't be a lot of extra work on you. If you aren't using a stored procedure already, they are much faster and far more secure than "on page" SQL. If you have insert statements in various parts of your application inserting to the same table, then you should be encapsulating them into a stored procedure anyway! Your DBA's job is to protect the efficiency and cleanliness of your database. Adding triggers unnecessarily affects both.