Incorrect Use Of Update Trigger For Audit Purpose
Jan 24, 2001
I have a update trigger on a table which fires trapping the userid and time in the same record. The code is below. The tirgger only works if I have the recursive triggers option unchecked on databae properties. Is the way I am trying the only way to accomplish this in the update trigger or is there an more efficient way. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
View 2 Replies
ADVERTISEMENT
Mar 28, 2008
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
View 3 Replies
View Related
Aug 11, 2015
I'm updating one column using trigger but i am getting below error .
UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
View 2 Replies
View Related
Mar 28, 2008
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
Code Snippet
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
View 6 Replies
View Related
Dec 5, 2006
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.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
Jun 5, 2007
hi, im kinda new to sql and was just wondering if someone could help.
i need to create a trigger that saves all changes from a table(table1) to an audit table.
-table1-
item_code
item_price
item_description
View 2 Replies
View Related
Oct 26, 2007
I wont to get text of statement which chaged actual table.
with this script :
Code Block
if object_id('dbo.mytable') is not null drop table dbo.mytable
go
create table dbo.mytable (f1 varchar(100))
go
alter trigger dbo.ti_mytable on dbo.mytable for insert
as
set nocount on
print 'in trigger'
DECLARE @sql_handle binary(20)
select @sql_handle = sql_handle FROM master.dbo.sysprocesses p (nolock) where spid = @@spid
select @sql_handle, @@spid, [text] FROM ::fn_get_sql(@sql_handle) as statement_text
GO
if I execute
Code Block
insert into mytable values ('test')
I getting text of the trigger. How I can get @sql_handle of original statement ?
View 1 Replies
View Related
Jan 23, 2008
Hi,
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
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
View 7 Replies
View Related
Nov 2, 2004
I need to create a simple audit trigger for a table with 12 columns. I need to determine which row was changed. Is there a simple way to do that. The table structure is
ID Integer(4)
barcode(25)
epc_tag(24)
bc_stop_flag(1)
reject_flag(1)
complete_flag(1)
hold_flag(1)
pe_1_flag
pe_2_flag
pe_3_flag
pe_4_flag
pe_5_flag
View 3 Replies
View Related
May 29, 2006
I have a trigger on my Table (MyTable). The Trigger saves any changes to mytable. The AuditTrails table has this columns: ID, TableName,PrimaryKey, OLDvalue, NewValue,UserID,DateCreated.
Now, how can i pass the application's UserID (Not the SQL Server User) and save it to the AuditTrails table?
In my .NET Application, if someone delete a record from myTable how can i possibly get the userid of that person using my Audit trail Trigger?
View 5 Replies
View Related
Jan 21, 2008
Hi all,
I'm trying to create a audit trail trigger. I'm new to SQL Server. For simplicity sake, let's just say I have a table like the following:
Table1
FieldA
FieldB
And the audit table is:
Table1_Audit
FieldA
FieldB
Operation (Insert/Update/Delete)
Operator (Username)
Op_Date (GetDate())
Ok, simple enough. Now, I'm using Studio Express and there are several templates available. By default this is the one I get:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
Not sure if I need all of this information? Examples I've found on the web look very confusing...ugh. I looking for simple (if possible).
Many thanks in advance,
Mark
View 2 Replies
View Related
Oct 14, 1999
Greetings
I'm clear about the use of a DELETE trigger to "move" your deleted record to a second database as a sort of recycle bin.
But SS7 has the limitation, and it's mentioned in BOL, that it cannot reference your TEXT, NTEXT or IMAGE fields in the DELETED table. It says to join the original table with DELETED to get at those fields.
The only problem is the original table's record has been deleted! Even though the transaction has not yet been COMMITTED.
Here's my Trigger:
CREATE TRIGGER AuditTest ON Activity FOR DELETE AS
INSERT AuditDB.dbo.Activity
SELECT Activity.* FROM Activity INNER JOIN Deleted
ON Activity.ActivityID = Deleted.ActivityID
And for discussion, here's my Table:
ActivityID uniqueidentifier
OrgId uniqueidentifier
Title varchar(600
Active bit
Comments text
LastUpdate datetime
Any suggestions? Has anyone been able to implement a DELETE Audit
Trigger on a table with BLOBs?
Thanks,
-Rich
Richard Hundhausen
Stuttgart, Germany
View 3 Replies
View Related
Dec 7, 2011
I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
In my opinion it's an error with the correct number of single ticks, but not sure.
The Code:
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
[code].....
View 3 Replies
View Related
Nov 26, 2007
Hi,
I am trying to figure out which of these option is best suited for auditing. Although each one of them has its own pros/cons.
CLR trigger is easy to write and can be made generic so that it fits for any table required to be audited.
I tried both the option in test database and i found the CLR trigger performed poorly.
Results were :
For table A (3 columns) with TSQL trigger took less than a sec for 2500 sequential inserts.
While table B (3 columns) having same structure with CLR trigger took more than 20 sec for 2500 sequential inserts.
Has anybody done performance comparision of this 2 approaches ?
Please share results if any.
I wanted to validate that is my findings correct so that i select best optimized approach.
Thanks,
Jignesh
View 1 Replies
View Related
Aug 30, 2013
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
View 1 Replies
View Related
Jul 6, 2015
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test](
[Customer] [varchar](12) NULL,
[Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values
)
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
View 6 Replies
View Related
Jul 11, 2015
SQL Version: SQL2014
PROBLEM: The SQL insert trigger code below is returning incorrect results. In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement. For instance the value returne for the Price_BeforeAdj field does not = 20000000? It returns a NULL. See code below.
OFFENDING CODE:
ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I]
ON [dbo].[budxcWIPAdjustments]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
UPDATE budxcWIPAdjustments
[Code] ....
View 11 Replies
View Related
Oct 25, 2004
Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?
They also want us to track schema changes. Is this possible?
Thanks, Dave
View 5 Replies
View Related
Jul 20, 2005
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'
View 1 Replies
View Related
Nov 1, 2014
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
View 1 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
Apr 22, 2007
Hi all
My error is as follows: Incorrect syntax near '('.Line 27: acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)Line 28: acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)Line 29: acceptOrDeclineFriendship.Update()Line 30: Line 31: End Sub
Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this:
SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC">This works.
Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests">
<ItemTemplate>
(other stuff like avatar and username etc)
<asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
<asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
</ItemTemplate>
</asp:Repeater>
The code-behind (VB) which deals with this is as follows: Protected Sub Accept_Decline_Friends(ByVal sender As Object, ByVal e As CommandEventArgs)
'retrieve id of requestee and the answer accept/decline
Dim friend_id As String = e.CommandArgument.ToString
Dim answer As String = e.CommandName.ToString
'add the parameters
acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)
acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)
acceptOrDeclineFriendship.Update()
End Sub
Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid)
This relates to a sqlDataSource on my .aspx page like this: <!---- update query when user has accepted the friendship ---->
<asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx %>"
UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @Response) WHERE (FriendID = @UserID) AND (UserID = @FriendID)">
<UpdateParameters>
<asp:ControlParameter Name="UserID" ControlID="userIdValue" />
</UpdateParameters>
</asp:SqlDataSource>
Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to.
I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values.
Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!
View 1 Replies
View Related
Nov 7, 2007
Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot. --------------------------------------------------------------------------------------------------------------------------- public bool Update( string newaccount, string newfullname, string rep, string zip, string comment, string oldaccount, string oldfullname ) { SqlConnection cn = new SqlConnection(_connectionstring); SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn); cmd.Parameters.AddWithValue("@newaccount", newaccount); cmd.Parameters.AddWithValue("@newfullname", newfullname); cmd.Parameters.AddWithValue("@rep", rep); cmd.Parameters.AddWithValue("@zip", zip); cmd.Parameters.AddWithValue("@comments", comment); cmd.Parameters.AddWithValue("@oldaccount", oldaccount); cmd.Parameters.AddWithValue("@oldfullname", oldfullname); using (cn) { cn.Open(); return cmd.ExecuteNonQuery() > 1; } }
View 12 Replies
View Related
Jul 20, 2005
I have tried many variations (after reviewing other posts) and can notresolve the following issue:RUNNING SQL MAINTENANCE----------------------------SET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET NUMERIC_ROUNDABORT OFFexec master..xp_sqlmaint '-D SBC -UpdOptiStats 10 -RebldIdx 10'--tried UpdOptiStats and RebldIdx separately with same resultsRECEIVE THE FOLLOWING MESSAGE------------------------------[Microsoft SQL-DMO (ODBC SQLState: 42000)]Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options haveincorrect settings: 'QUOTED_IDENTIFIER, ARITHABORTSERVER SETUP-------------------------------Windows 2000, Service Pack 4SQL Server 2000 Standard Edition, Service Pack 3Any help is greatly appreciated.
View 1 Replies
View Related
Oct 1, 2007
Hi, i have problem as subject says.
Db has table with 3 columns, ID, Key and Val. ID is primary key, Key has unique index and Val simple holds value in text format.
I have created DAL layer using .netTiers and CodeSmith. Generated procedures.sql has before every procedure set ANSI_NULLS to OFF.
When i read rows from table i print them on screen. When user changes value, that should also be updated in database.
When i select entity, its value is changed.
Here is code snipper.
Code Blockentity.Key = key;
entity.Value = value;
TransactionManager transactionManager = DataRepository.Provider.CreateTransaction();
try
{
transactionManager.BeginTransaction();
retVal = DataRepository.TestTableProvider.Update(entity);
transactionManager.Commit();
}
catch
{
transactionManager.Rollback();
throw;
}
I got an exception with message below:
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Also, class which hold previous code snipper supports caching, by using some kind of SqlCacheManager, which is above SqlCacheDependecy class. Database service broker is started by
Code Block
ALTER DATABASE <DB_NAME> SET ENABLE_BROKER
Server is SqlExpress 2005.
Application is in ASP.NET 2.0.
I also noticed next.
On first run, previous code passes without errors. On second run, update error appears.
This is log from sql server.
Code BlockQuery notification delivery could not send message on dialog '{822C7891-736E-DC11-836B-005056C00008}.'. Delivery failed for notification '<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationhttp://schemas.microsoft.com/SQL/Notifications/QueryNotification">http://schemas.microsoft.com/SQL/Notifications/QueryNotification</A< A>>" id="1" type="change" source="database" info="restart" database_id="13" sid="0xE7C0751C9F7F6C4D9423096BBCC7FB69"><qn:Message>edd3e2dd-11ed-4d92-a0f4-5c674a90aecf;8b2095663cc6a9c297120e4c94d488555e97e54d</qn:Message></qn:QueryNotification>' because of the following error in service broker: 'The conversation handle "822C7891-736E-DC11-836B-005056C00008" is not found.'
Need fast answer.
Thanks in advance.
View 5 Replies
View Related
Jan 18, 2006
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
May 4, 2007
Hi,
Here's the code I've used to try and update a new user's IP Address to a Table called Customer who's key field in the UserId:
Getting the Exception Error "Incorrect Syntax near'('. " Any ideas?
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser(); //This gets the active user if there is someone logged in...
Guid UserId = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in user
string IPAddress = Request.UserHostAddress.ToString();//This gets the IPAddress of the currently logged in user
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
using (System.Data.SqlClient.SqlConnection con =new System.Data.SqlClient.SqlConnection(cs))
{
con.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE Customer SET(IP_Address = @IP_Address) WHERE (UserId = @UserId)";
cmd.Parameters.Add("@UserId", System.Data.SqlDbType.UniqueIdentifier).Value = UserId;
cmd.Parameters.Add("@IP_Address", System.Data.SqlDbType.Char, 15).Value = IPAddress;
cmd.ExecuteNonQuery();
con.Close();
}
Thanks.
View 5 Replies
View Related
May 30, 2008
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,
View 3 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Dec 17, 2001
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end
View 1 Replies
View Related
May 30, 2008
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)
View 7 Replies
View Related
