I Have create a trigrer for my table transhd to update something in my detail table..
when i updated one record the trigger works fine..
but when i run command to update a batch of record in header
using command [update XXXX where cont] the trigger only fire for the last record in the batch..
is this the ms sql default behavior.. anyway to the around it
I Have create a trigrer for my table transhd to update something in my detail table..
when i updated one record the trigger works fine..
but when i run command to update a batch of record in header using command [update XXXX where cont] the trigger only fire for the last record in the batch..
is this the ms sql default behavior.. anyway to the around it
strsql = " select * from dic where english='" & SearchBox.Text Or TextBox4.Text & "'"The machine said Input string was not in a correct format. How can i select database with or operator in asp.net .Is it possible to select database in sql from more textbox ?
hi,I wrote this sp :CREATE PROCEDURE ins_AddOrder( @CartId NVARCHAR(50), @CustomerId INT, @OrderDate DATETIME, @OrderId INT OUTPUT)AS DECLARE @ins_error INT, @sel_error INT, @var_rowcount INT BEGIN TRAN AddOrder INSERT INTO tbl_orders( customerId, orderDate)VALUES( @CustomerId, @OrderDate) -- SET THE @@ERROR VALUE TO @ins_errorSELECT @ins_error =@@ERROR /*IF INS_ERROR != 0BEGIN ROLLBACK TRAN RETURNEND*/ SELECT @OrderId = @@identity INSERT INTO tbl_orderDetails( orderId, productId, quantity, productPrice)SELECT @OrderId, tbl_product.productId, quantity, tbl_product.productPriceFROM tbl_shoppingCartINNER JOIN tbl_product ON tbl_shoppingCart.productId = tbl_product.productIdWHERE cartId = @CartId -- SET THE @@ERROR VALUE TO @sel_errorSELECT @sel_error = @@ERROR -- SET THE @@ROWCOUNT TO @var_rowcountSELECT @var_rowcount = @@ROWCOUNT IF @sel_error <> 0 OR @var_rowcount = 0 BEGIN ROLLBACK TRAN AddOrder RETURN ENDELSE BEGIN EXEC del_EmptyShoppingCart @CartId COMMIT TRAN AddOrder ENDGOIn the last few lines i try to prevent adding new row to tbl_orders (first statement), while the SELECT (second statement) does not return any row .It still not work.Can anyone help please?thanks.
I have a login in my sql server with windows authentication.When i tried to connet to the sql server with that login its not allowing me to connect even though I was logged on into my system.What should be the reason and what should i do?
I have met a problem in the ASP.NET project creation with SQL Server Database Connection. "Can't Login to My ServerName/ASPNET How to solve the problem?
Hi , I just put my files up on a web server. I can view my aspx pages..but when I try to login to the website I get the following; An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Can you help? I can understand that it cannot find the specified instance of sql server, however I don't remember ever configuring it look to at a specific sql server. When i created the site in web dev express edition and added the login controls and set the asp config tool to use internet security it created the database itself and so I didn't have to put a connection string in. I also cannot see a connection string in tthe web.config file. The aspnetdb.mdf file is there on the server. is this something i need to configure with the hosting company or do i set it in my webconfig file. Thanks
I need to create some tables dynamically using sp_executesql. But the problem is the length of sql string is more the 4000 characters. How can I solve this problem? Thanks
hello ![color=blue]>>> I am trying to intstall Mysql binary version with installor in Windows> XP machine . But it is not working . i have installed in the default> path ,in windows service componenet its showing server is running,since> it is mysqld-nt so name-piped servr is also running.>> But still client is able to connect to server.>> when i am tring to instal manually it is showing>> " service already exists;> the currect server intstalled is c:mysqlinmysqld-nt.exe>> when trying to connect to server> C:> c:mysqlinmysql test> ERROR 2003 : cannot connect to MYSQL server on localhost <10061>>> C:> telnet localhost 3306> connecting to localhost.......> could not open connection to host,on port 3306No connection shb ne made> because the target machine actibvely refused it.>> plz help me in installing ,pls clarify the problem.> pls reply soon.>>> regards> Smita[/color]
We have a config table which has columns like pollingfolder, cop to folder etc. Data looks like this Config id polling folder 1 d:dataloadacn 2 d:dataloadship under ACN and Ship folders we can have more than 1 source files which had to be loaded into target the load process has to be repeated for each file in all the folders like ACN and ship I have used for each loop container to poll the folder. Can we set the directory to be polled at runtime taking from config table. For doing this I have used the property Directory in expression editor of for each loop. But it is not working. If I hard code the variable value in variables section , then its working. How to change the directory to be polled value at runtime?
I had installed SQL server 2005 4 bit version. I created on SSIS package through export wizard.
the SSIS packages will transfer data from database to Excel file. But it is giving the below error.
Message Executed as user: XXXXXSYSTEM. ... Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 07:00:00 Error: 2008-05-14 07:00:00.51 Code: 0xC0202009 Source: Interface Connection manager "DestinationConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-05-14 07:00:00.51 Code: 0xC00291EC Source: Preparation SQL Task Execute SQL Task Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 07:00:00 Finished: 07:00:00 Elapsed: 0.344 seconds. The package execution failed. The step failed.
I have two data sets but from the same data source. I created two different tables from these two data sets. Now I want to include a chart too. But When I insert the chart, it gives a erorr message.
The dinfinition of the report/my report name is invalid. More than one data set, data region or grouping in the report has the name'chart1_seriesgroup1'. data set, data region and grouping names must be unique within a report.
Someone, please help me understand: I have the following code: Dim conString As String = ConfigurationManager.ConnectionStrings("WebAllianceConnectionString").ConnectionStringDim dsrc As New SqlDataSource()dsrc.ConnectionString = conStringdsrc.SelectCommandType = SqlDataSourceCommandType.StoredProceduredsrc.CancelSelectOnNullParameter = Falsedsrc.SelectCommand = "sp_GetCustomerInvoiceList"dsrc.SelectParameters.Clear()dsrc.SelectParameters.Add("@QueryType", "DATE")dsrc.SelectParameters.Add("@CustCode", "BAM7")dsrc.SelectParameters.Add("@OrdNumber", " ")dsrc.SelectParameters.Add("@InvStartDate", "1/1/2005")dsrc.SelectParameters.Add("@InvStopDate", "1/31/2005")dsrc.SelectParameters.Add("@PONumber", " ")Return dsrc.Select(DataSourceSelectArguments.Empty) When this runs, it throws an exception:Prodecure or Function 'sp_GetCustomerInvoiceList' expects parameter '@QueryType', which was not supplied. I'm confused... I clearly added that using the .Add method?What am I doing wrong?
has anyone had a problem restoring a SQL 2000 without sp1 to a SQL 2000 sp1 box or vice versa? I have had 2 failures telling me to call MS support in the message?
hi everyone, How do i declare a global variable in my package which takes a numeric value like User::VAR1 = 200402 and later on work on it
Later in the properties of the Dataflow i want to have this expression..
"select * from " + "TAB1" + " where Date=" + @[User::VAR1]
Here i want to subtract 190000 from @[User::VAR1] to get it in to myformat i.e the DATE format in the table
I can only see String datatype and othe datatypes wont allow me to to do any kind of manipulation in the expression: and to be more specific what are Int16 Int32 Int64 Double
I tried to use all of the above but the expression doesnt allow me as once i use any of the above it says :
TITLE: Microsoft Visual Studio ------------------------------
Nonfatal errors occurred while saving the package: Error at Extract: The data types "DT_WSTR" and "DT_I8" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
I have the same problem like Ben. Trying to connect to database engine but failed all the time. I use a SQL 2005 Enterprise Eval version. When I connect to database engine, I put <my computer name>SQLEXPRESS, it gives me this message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
I checked the sqlbrowser and it's running. I also enabled port 1434 in Firewall. But it still doesn't work. However, when I connect to analysis service using just <my computer name>, it lets me connected. I tried many ways to fix this problem but still no luck. Could anyone help me solve this problem please? Thanks so much.
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called 'Audit' with columns 'ActionType' //varchar 'TableName' //varchar 'PK' //varchar 'FieldName' //varchar 'OldValue' //varchar 'NewValue' //varchar 'ChangeDateTime' //datetime 'ChangeBy' //varchar
using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;
public partial class Triggers { //A Generic Trigger for Insert, Update and Delete Actions on any Table [Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger() { SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context string TName; //Where we store the Altered Table's Name string User; //Where we will store the Database Username DataRow iRow; //DataRow to hold the inserted values DataRow dRow; //DataRow to how the deleted/overwritten values DataRow aRow; //Audit DataRow to build our Audit entry with string PKString; //Will temporarily store the Primary Key Column Names and Values here using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection { conn.Open();//Open the Connection //Build the AuditAdapter and Mathcing Table SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn); DataTable AuditTable = new DataTable(); AuditAdapter.FillSchema(AuditTable, SchemaType.Source); SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us //Get the inserted values SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn); DataTable inserted = new DataTable(); Loader.Fill(inserted); //Get the deleted and/or overwritten values Loader.SelectCommand.CommandText = "SELECT * from DELETED"; DataTable deleted = new DataTable(); Loader.Fill(deleted); //Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire) SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn); TName = cmd.ExecuteScalar().ToString(); //Retrieve the UserName of the current Database User SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn); User = curUserCommand.ExecuteScalar().ToString(); //Adapted the following command from a T-SQL audit trigger by Nigel Rivett //http://www.nigelrivett.net/AuditTrailTrigger.html SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" + TName + @"' and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn); DataTable PKTable = new DataTable(); PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table { case TriggerAction.Update: iRow = inserted.Rows[0];//Get the inserted values in row form dRow = deleted.Rows[0];//Get the overwritten values in row form PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns { if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "U";//U for Update aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry } } break; case TriggerAction.Insert: iRow = inserted.Rows[0]; PKString = PKStringBuilder(PKTable, iRow); foreach (DataColumn column in inserted.Columns) { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "I";//I for Insert aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = null; aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; case TriggerAction.Delete: dRow = deleted.Rows[0]; PKString = PKStringBuilder(PKTable, dRow); foreach (DataColumn column in inserted.Columns) { //Build and Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "D";//D for Delete aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = null; aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; default: //Do Nothing break; } AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable conn.Close(); //Close the Connection } }
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values //and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......" public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow) { string temp = String.Empty; foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed { temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,")); } return temp; } }
The trick was getting the Table Name and the Primary Key Columns. I hope this code is found useful.
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger on a for insert as insert into b(ID)
When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.
Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
Hi, I am not sure if this is the right forum to post this question. I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio. When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H". This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table. What else would cause the database automatically change my update? Could there be any other place I should look for an update trigger on this table? Thanks,
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers. The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name how do i get around this?
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data. For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success. Please Help me.
There are two different Stored Procedures insert data into a table, is it possible to catch what stored procedure name was that trigged the trigger in the trigger?
Hi, I am trying to script a trigger on a table to accomplish the following... If a row is updated then the value in the 'date' column in that specific row would update to getDate(). I have never created a trigger before and finding it very difficult. Thank you very much in advance. Kind Regards, Sean.
Hello I am having difficulty try to get a trigger to work. I need to create a trigger querie that will insert a row from TableMain (Column LNames) table to TableA (Column LNames) Below is my querie
CREATE TRIGGER [trig_addTable_A]ON Table_A FOR INSERT ASINSERT LName (LName) SELECT LNameFROM TableMain inserted I have inserted test names into the main table but nothing in table A has inserted. What have I done wrong. Thanks
I am looking for a INSERT trigger example, to achieve the following objective: I would like the trigger to be fired when a row gets inserted into a table. I want to retrive some value from the inserted row and use this value to insert a new row in table 2. Thanks