Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?

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.


10/13/2006 4:33:45 PM 29906 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 12


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...


"Trust in the Lord and do what is good; dwell in the land and live securely. Take delight in the Lord, and He will give you your heart's desires" (Psalm 37: 3-4).
10/13/2006 4:55:18 PM

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.

Tony Alicea
clarity of mind and creativity in application software development...
10/14/2006 2:49:25 PM

Similar Artilces:

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?    The direct answer to your question is: You insert it like this:   ...

Empty 'inserted' table in insert trigger
I have a table with insert trigger. Inside the trigger, I write records from the inserted table to another table. However, I found that the inserted table is empty. Any Idea? I am using ASE Thanks in advance. not impossible. something like: insert tbl select * from other_tbl where 1=2 would fire the trigger, but not insert any rows, and have no corresponding rows in the inserted table. <Vicky> wrote in message > I have a table with insert trigger. Inside the trigger, I > write records from the inserted tabl...

Error: Primary key for row in table 'x' is referenced by foreign key 'f' in table 'y'
(ASA 9 newest ebf) I'm getting this error even though mentioned foreign key has cascade updates (and deletes too) enabled. Is this some new behaviour in asa 9, or why can i get such message for cascaded foreign key? Any ideas? Thanks Pete Found a reason for it...but should it really work so? It's this way: - table a has columns a,b,c - table b has columns a,c, and references columns a,c (with a,c) in table a with cascade updates - table a hes before update trigger to change columns a when column b is changed (set new_row.a = ...) and that set new_row.a = ... i...

Data Controls insert rows into gridview then insert rows from gridview into SQL Table
Hello there, I'm still new at performing miracles with code . Currently I am having trouble with my latest coding adventure I have a web application which records jobs and labour assigned to jobs. The issue in particular I'm having is the way in which labour is to be assigned. I want to use standard controls (Textbox, Dropdownlist) to record the values into a gridview (ie,put multiple rows into the gridview using the controls). Then, when all the inserting is complete, the user clicks a submit button to insert all the rows from the gridview into a database table. The reason for...

DetailsView Insert Error: Cannot insert the value NULL into column 'customer', table 'DEMO
Using VWD Express edition creates a page(Default.aspx) with a Dropdownlist for selecting a customer and a GridView to show the details of the selected customer and to edit and delect a customer's detail. I add a button to link to page Add.aspx for add a new record. I add a DetailsView and the following code so it goes to New record mode when page loads:        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)               DetailsView1.ChangeMode(Det...

On update trigger I can't show rows in the inserted and deleted tables
Hi all: I have two tables named A and B. The first has an after insert trigger with an "update B set......" statement in the source, and the second table has an after update trigger. Both insert and update work fine but when I check the rows from inserted or deleted tables inside of after update trigger on table B its haven't rows. Both triggers was created using transact-sql syntax on sql anywhere 7.0.4 build 3519 Any help would be appreciable Thanks in advance Hern=e1n inserted and deleted on work in T-SQL triggers. To code a T-SQL trigger you need to use ...

How do I 'join' tables in ADO.NET?
I am attempting to create an ASP.NET 3.5 application (with a SQL Server database) using Visual Web Developer Express 2008. I am an utter beginner to ADO.NET and need some basic guidance on how to make selective updates to a table based on a condition in a related table. With a previous background in MS Access and VBA, I was expecting that I could use an ADO.NET SqlCommand string such as shown below. However, this generates an exception error: 'Incorrect syntax near the keyword 'LEFT'. I suspect that I am suffering from some major misconceptions about how to achieve this sor...

update 'inserted' table in trigger
Hello All, i have some calculated fields while inserting. is there any way to update the 'inserted' table in trigger? or the only method is to write a stored-proc to do 'calcule then insert' Thanks. Alien Chen 05/14/2002 wrote... > Hello All, > > i have some calculated fields while inserting. > is there any way to update the 'inserted' table in trigger? > > or the only method > is to write a stored-proc to do 'calcule then insert' Within the trigger you would use the INSERTED table to f...

Error in Tutorial DetailsView"Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSERT fails.
Hi,I am trying to do the insert part of detail's view Tutorial (the last one) and I received this error: "Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSERT fails.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSER...

getting the error: Cannot insert the value NULL into column 'name', table 'InterGlobe.dbo.IG_Admin'; column does not allow nulls. INSERT fails. The statement has been terminated.
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <script runat="server">   private void CrLogin_Click(object source, EventArgs e) {   try { //Response.Write("hello"); SqlDataSource2.Insert(); // Response.Redirect("logincreated.aspx"); } catch(Exception ex) { Response.Write(ex.Message); }   } </script>       <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">       &nbsp;<a...

Catching SQL Server Error Before It Happens: “Cannot insert duplicate key row in object 'dbo.tablename' with unique index 'idxName'.
Environment as follows:Language – VB.Net – 2.0Visual Studio 2005SQL Server 2005  I have an SQL formview (formview1) connected to an SQL Server data base.  There is an insert template in the formview which is used to insert new records to the sql server table.  The table has a strict no duplicates index consisting of the following fields:  year (int), mcu (nvarchar) and gmcatcd (nvarchar).  Formview1 has the following controls which correspond to the sql server data base table:  yearTextBox, mcuTextBox,gmcatcdTextBox.  I have an existing s...

How to call or retrieve fields from sql and display in table without using, I'm using a dynamic link library....Is the coding different?
Help me! I'm doing my school project on Software Change Management Tool, CR Tracker. I want to display table in the webform, and the table data is retrieve form the sql server. So how do I call the sql so as to display the data in a table? hy..What does that dll contain ?Does it already contains relevant code to connect to a database ? Have a look hereThanksDinuj Nath The easy way to do this is with the use of a data bound grid. The Grid can be customised to look and feel you like. You will need to add a data adapter to your page and a grid. When you setup the data adapter you will nee...

How to solve error : Column 'Column' does not belong to table 'Table' ? Code attached
Hello    I have a dataset which is successfully being filled. However whenever i try    to access a givewn row of one of the tables in the dataset i get the error    "Column 'ContactId' does not belong to table 'SalesAgent'"    Except that it does and if i use an ordinal reference i can access the value    of the column. If i print the schema sure enough the column is called    ContactId. I have also noticed that if i hover the cursor over the    contactrow i can ac...

ASA 7.03: User ' another user' has the row in table 'tablename' locked' ????
Error message ASA 7.03: >>>User ' another user' has the row in table 'tablename' locked' <<<< We are using ASA 7.03 on WIN-NT with two processors and 2 GB RAM. Page-Size of database is 4096 ! We have always the above error.messages. But when I call sa_locks(table_name='tablename' ) it shows, there is no table or row locked. We have also called sa_locks(0). But there are no locks. The table has only 300 rows !! It happens AFAIK this means you've run out of locks, where the limit is approximately 10,000,000 f...