My requirement is to get the autoincrement column once a new row is inserted, we need the autoincrement value to update other tables, at present I am using an insert trigger in which I am extracting the autoincrement column from the 'inserted' table, but how far this work perfectly when multiple users insert simultaneously. Can any of you suggest me the best way to extract the actual value inserted.
Now the scenario is :
sp which insert a row
Begin tran
insert ...
select @returnKey = (select retkey from #temptab)
drop #temptab
Commit Tran
Trigger on insert
insert idcolumn into #temptab select autokey from inserted
If user A & B inserts row exactly at same time, will this method return the exact auto value what A and B have inserted to them respectively.
I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!
Thank you!
My trigger: CREATE TRIGGER tr_SendPassword ON PIPEmail FOR INSERT AS DECLARE @Password varchar(100) DECLARE @EmailAddress varchar(100) DECLARE @message varchar(100) IF (select count(*) from inserted) = 1 BEGIN IF exists (SELECT * FROM inserted WHERE Subject = 'Forgot my password') BEGIN select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password, @EmailAddress = [User].Email from UserRole join inserted on UserRole.WindowsUser = inserted.WindowsUser join [User] on [User].WindowsUser = UserRole.WindowsUser
exec master.dbo.xp_sendmail @recipients=@EmailAddress, @subject='SAR Search password request', @message=@Password END END
Hi,I'm a newbie to sql server and this may be a really dumb question forsome you. I'm trying to find some examples of sql server triggers thatwill set columns (e.g. the created and modified date columns) if the rowis being inserted and set a column (e.g. just the modified date column)if the row is being updated.I know how to do this in oracle plsql. I would define it as a beforeinsert or update trigger and reference old and new instances of therecord. Does sql server have an equivalent? Is there a better way to dothis in sql server?Thanksericthis is what i do in oracle that i'm trying to do in sqlserver...CREATE OR REPLACE TRIGGER tr_temp_biubefore insert or updateon tempreferencing old as old new as newfor each rowbeginif inserting then:new.created_date := sysdate;end if;:new.modified_date := sysdate;end tr_temp_biu;
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
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
Does anyone know how to use for xml auto that will format an xml response to show the parent/child relationships? I currently have the parents and children in a temp table and can only get a formatting so that each one is returned at a parent. how can i select the results from the temp table so for xml auto will return the properly formatted xml file?
The upgrade adviser for for 2k5 says something about derived tables being handled differently between 2k and 2K5 and it says to query the tables directly but this does not seem to make much sense because I thought FOR XML AUTO just created some generic XML for presentation purposes. These 2 stored procedures that it is complaining about do query the tables directly and they use the FOR XML AUTO to control the output.Does anyone know if I have to worry about this? I am tempted to let this slide and check out this part of the application after the migration happens tomorrow for QA to start testing.Yes I have been googling, checking my books and digging around in BOL. I am not seeing anything.DISREGARD: I found my derived table. It appears to change the output of the XML. Perfect.
I am using sql server 2000 and want to know how to get xml out of the database that looks like this using for xml auto <Clients> <Client ID="1"> <Employer="Company1" /> <Employer="Company2" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client> <Client ID="2"> <Employer="Company3" /> <Employer="Company4" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client></Clients> The problem I am having is that Contact is nested inside employer when I select Employer before Contact and the opposite happens when I select Contact first. They both join to the Client table so I would assume they both should nest directly under Client. How do I get different fields to nest directly under the same element like above?
I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and based on expirydate I have to generate the mails automatically,Please guide me to solve this issue. where we have to run the stored procedure. Do we have to use jobscheduler? please guide me how to use it Thanks in avance regards, Raja.
Hi all, I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it. So if I for example select all Dates older than today's date, I would want something like this:
1 10/12/2006
2 10/18/2006
3 10/20/2006
4 10/22/2006
5 10/30/2006 Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually". Is this possible? And if yes, how ? :)
Hi All, I want to write a console application to send email. There is a Date field in the SQL Server and I need to send email 2 weeks before that date.I have no idea how to write a console application and make it work.Does anybody have code for this? If so please post it. Thanks a lot, Kumar.
It's been a long time since I've had to check an index for the highest value, then add 1, to create a new unique key. These past few years, it seems this is usually done for you. But now that I'm working with MS-SQL, I don't see it. Is it there? It's doesn't seem to be inherent in the definition.
Hello, Firstly Hello to everyone I'm new the forum and fairly new to .net I'm working on web datbase application using visual studios 05 and MS SQL05 I've used 2003 (briefly) before but 2005 is very new to me. To my problem I download the GUI interface from microsoft so I can now setup a local database and do my own testing. I have created the table and fields with in it however on a particular table i have made a primary Key and left it as an INT but I would like to set it as auto increment ! I dont know how to select that option as i was used to mysql way of doing things or does this have to be done as a stored procedure ? Any assistance much appreciated.
I am very new to using SQL server 7. I've always used mysql in the past. I cant figure out howto create a autoincrementing key for my tables... is it possible to do in SQL7?? If so.. how.. i thought you just set the datatype to auto increment etc...
