I'm sure this is a simple question, but I haven't had any luck finding an answer.
With a trigger, how do I access the modified/updated/deleted rows? The point of all this is I need to a database that maintains lockout procedures to email certain people when one of these procedures gets updated/added. I want to be able to build a link that points to our webserver (the interface for the lockout db) and to do this, I need certain fields of the affected records.
Let me know if I need to clarify anything. Any help is greatly appreciated.
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005 --i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here? -i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how? cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue; Please help me out with this.Thanks
Hello,If I run an action SP from MS Access using ADO:...cmd.executewhere the SP is something like Create...Update tbl1 set fld1 = 'something' where...how can I retrive the count of records affected like from Queryanalyzer?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Hey All I wish to display the 3rd and 4th to last records of a table on a page. Is there anyway of doing this? I can access the last records by using Count and TOP 1 but i find that count - 1 as the ID to select can be errornous if records are removed from the table. I also tried using ORBER BY followed by LIMIT but kept getting an error in order by clause message. Any help would be greatly appreciated Thanks and Regards Drew
Would anyone be able to advise me on the best way in which i am able to display the information from a Search in sqlce database and allow the results be displayed in a specified location.
An example is in my program there is a list of id which the user can search. They search for a record by id so only one record will return. I would like this information to then be displayed in the textboxes which are on the screen.
Any problems understanding what i am trying to do please ask. Any help would be appreciated
Hi.I need to make a trigger which fires when after an UPDATE is done ontable A in database X. The trigger must perform a statement whichcopies some of the data from the update to table B in database Y onthe same server.My question is: Can I access the data which caused the trigger to fireor just get an indication of which entry in table A had been updated?Best RegardsJens Christian Andersen.
Hi, My full text search on 2 millions records is taking time to show the result. I have created full text catalog in RAM drive to make the retrival process faster. But still its taking more than 1 minute to get the matching pattern. I am using SQL server 2005. I have 2 columns (id,text) in my table..
This is my unique index script
USE [SAMPLE] GO CREATE UNIQUE NONCLUSTERED INDEX [ui_productid] ON [dbo].[Products] ( [id] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my primary key index script.. USE [SAMPLE] GO ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my query..
SELECT D.[id], D.productname FROM dbo.Products AS D WHERE CONTAINS(productname, 'ford')
What should i do to show the result in 3-4 seconds.
1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2 fields(chat_id ,country), chat table will store all countries data (India,china,taiwan)
2) chat_id,language will be populated by insert statement, and chat_info by update statement, i have a below after update trigger on chat table,which should verify country from media table and copy respective record from chat table to chat_country table (chat_in,chat_cn,chat_tw - these tables will have same structure as chat table)
3) this trigger is not firing for some of the records ,no clue or info for which it is missing some records.
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat] after update as BEGIN
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. no effect; the user makes their own login id but that does nt matter either.
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
Note: The following fields have their Default Value set to (0) SystemID EventStatusID Coop NewProductFlag TVSupportFlag RadioSupportFlag FSISupportFlag RollbackPricing PleaseContactFlag
Default Value set to (1): KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT AS IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int DECLARE @EventID int DECLARE @AccountID int DECLARE @BillingContactID int DECLARE @EventName varchar(100) DECLARE @EventStatusID tinyint DECLARE @Coop bit DECLARE @CoopSupplier varchar DECLARE @SamplesPerDay int DECLARE @BrochuresPerDay int DECLARE @AverageDailyMovement int DECLARE @SalesGoal int DECLARE @NumberofDays int DECLARE @NumberofHours int DECLARE @NumberofStores int DECLARE @WeekNumber tinyint DECLARE @PreferredHourID tinyint DECLARE @PreferredHourother char(20) DECLARE @PreferredDate1 varchar(20) DECLARE @PreferredDate2 varchar(20) DECLARE @NewProductFlag bit DECLARE @TVSupportFlag bit DECLARE @RadioSupportFlag bit DECLARE @FSISupportFlag bit DECLARE @RollbackPricing bit DECLARE @PleaseContactFlag bit DECLARE @EventStoreSelection tinyint DECLARE @EventClusterID int DECLARE @KitInformationID tinyint DECLARE @KitDescription varchar(1000) DECLARE @KitOther varchar(200) DECLARE @MCProgNum varchar(7) DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED SELECT @EventID = EventID FROM INSERTED SELECT @AccountID = AccountID FROM INSERTED SELECT @BillingContactID = BillingContactID FROM INSERTED SELECT @EventName = EventName FROM INSERTED SELECT @EventStatusID = EventStatusID FROM INSERTED SELECT @Coop = Coop FROM INSERTED SELECT @CoopSupplier = CoopSupplier FROM INSERTED SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED SELECT @SalesGoal = SalesGoal FROM INSERTED SELECT @NumberofDays = NumberofDays FROM INSERTED SELECT @NumberofHours = NumberofHours FROM INSERTED SELECT @NumberofStores = NumberofStores FROM INSERTED SELECT @WeekNumber = WeekNumber FROM INSERTED SELECT @PreferredHourID = PreferredHourID FROM INSERTED SELECT @PreferredHourother = PreferredHourother FROM INSERTED SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED SELECT @NewProductFlag = NewProductFlag FROM INSERTED SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED SELECT @RollbackPricing = RollbackPricing FROM INSERTED SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED SELECT @EventClusterID = EventClusterID FROM INSERTED SELECT @KitInformationID = KitInformationID FROM INSERTED SELECT @KitDescription = KitDescription FROM INSERTED SELECT @KitOther = KitOther FROM INSERTED SELECT @MCProgNum = MCProgNum FROM INSERTED SELECT @rowguid = rowguid FROM INSERTED
we have various user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)
This field is a drop down list , after insert committed , i want other user defined field to populate.
Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be populated.
CREATE TRIGGER User_Defined_field_KAE ON [dbo].[AMGR_User_Fields_Tbl] AFTER INSERT
AS
INSERT INTO [dbo].[AMGR_User_Fields_Tbl] (Client_Id, Type_Id, name SELECT Client_Id, '1089', A.USER_PHONE_1 FROM inserted , [dbo].[ADMN_User_Details] as A WHERE name = a.user_id;
IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL, [DateCreated] [datetime] NOT NULL, [TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress] FOR UPDATE AS DECLARE @rc AS INT ;
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,I set as follows:set nocount offWhat and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?Thank you.
I'm doing an executenonquery() and the value I'm getting back is -1 This is confusing me, as sometimes the row has updated when I look at it in sql manager and then I look at it again and it seems to have reverted.Would it be possible to point me in the right direction for the cause and how to rectify / debug this issue. I'm using a stored procedure and I'm slightly uncertain how to check exactly what sql server has been told to insert ;) Thanks!
After executing a restore command. I got the following result. Anyone know why is displaying rows affected. I saw the executiong plan and it shows several execution plans? Its the first time i see it. Can anyone explain what is going on?
restore database test_GG_ATRECORDING_QAT2 from disk = 'f:a.bak' with move 'a' to 'f:MSSQL$INAQATdataa2.mdf', move 'a_log' to 'f:MSSQL$INAQATdataa2_log.LDF'
Processed 11016 pages for database 'test_GG_ATRECORDING_QAT2', file 'a' on file 1. Processed 1 pages for database 'test_GG_ATRECORDING_QAT2', file 'a_log' on file 1.
I work on a local copy of a database (Access 2000 MDB with ODBC linkedtables to a SQL 7 database). The SQL Server db I'm working with is on my Cdrive (MSDE). Occasionally I'll connect to a VPN to do something on a remotecomputer, using PC Anywhere to perform the task. Doesn't involve my front orback end at all, except that the computer I'm VPNing to has a SQL databaserunning.OK, so I'm using my db; I connect to the VPN, and do my thing. Everything'sfine. However, if I then disconnect from the VPN (or if the VPN connectiontimes out and disconnects itself), I then can no longer access my local SQLServer database. My Access application still works fine, as long as itdoesn't have to look at any data. When it does have to look at data, I getODBC call failed. I have to close and reopen my Access database, and theneverything is fine.So, it seems that, for some reason, when I connect to the VPN, my local SQLServer or the ODBC driver or something in the mix is looking at that remotedatabase as part of what I'm using. Then, when the VPN connection is closed,it won't access my local database for some reason.Oh, and my local database and the remote database both have the same name.So that may be part of the problem.Any ideas as to what's going on?Thanks!Neil
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty
I am attempting to add a new row to a table. Every time I add the new row (doens't matter where) it has an effect on the height of the other rows in the table when displayed on screen (makes them taller). However the rows retain the required height when printed. I haven't any idea what might be causing this behaviour. The only explanation I can think of is that this is a bug.
I know it's a bit obscure but has anyone else come across this or have any idea what might be causing it?
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows... SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application. So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
Hi I have 400 mb db with simple recovery and 1 mb log file running on sql 2000 sp3
database reorgenazed with shrink db option once a day at 6 PM as part of maintance plan
db used with Powerbuilder application application and db structure was not changed for last 6 month.
Some users complaining about performance. When I run trace I don't see any query running more then 0.5 sec, while users complain it takes 3-5 sec to open a window or application freeze. When I ask user reconnect and repeat the task ,everthing is fine.
