How To Display Column From Inserted Table
Jul 31, 2007
Hi all,
I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:
Create Trigger Tg_Insert on [tablename]
FOR Insert
AS
Select * FROM Inserted
---------------------------------------------------------
Assume there is nothing wrong with this trigger as I have already checked the output with Query tool.
My question is how can I display the newly inserted data to the client? In my case, Console application.
I want to do something like Console.WriteLine("data from inserted table here");
How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005.
Thanks in advance.
Regards,
SHAH
View 11 Replies
ADVERTISEMENT
Aug 22, 2013
I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Code:
CREATE TABLE DaOldTable(
pkCHAR(10)NOT NULL,
a_columnCHAR(10)
)
CREATE TABLE DaNewTable(
idINTNOT NULL IDENTITY,
[Code] ....
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code:
INSERT INTO DaNewTable(a_column)
--OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
SELECT a_column
FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable
DROP TABLE DaNewTable
DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
View 3 Replies
View Related
Mar 22, 2008
I am using Stored Procedures and C# to create my very first web app (VS2005 Professional, C#, SQLServer 2005). I am wanting to display the users just inserted details via the form view in read only mode. I have read through several partial examples, but I am still unsure as to how to call the id of the just inserted record and use that ID to display the details in the Form View as many examples do not use stored procedures and make use of VB code (I am using C#) and the SqlCommand. I am pretty sure that I do not have to use SqlCommand as I am using an object data souce with a DAL – but I could be wrong, as I am a noob. My stored procedure reads as follows: ALTER PROCEDURE dbo.usp_ResumeNameDetailsInsertFV @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @UserID uniqueidentifier AS INSERT INTO [ResumeNameDetail] ([FirstName], [MiddleName], [LastName], [UserID]) VALUES (@FirstName, @MiddleName, @LastName, @UserID) RETURN SCOPE_IDENTITY() The stored procedure is called through the object data source and inserts the record as it should. I have manually tested this through the execute stored procedure in VS Server tab. Below is my Object Data Source: <asp:ObjectDataSource ID="ObjectDataSourceResumeNameDetailsFV" runat="server" DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="{0}" SelectMethod="GetResumeNameDetailsFV" TypeName="ResumeTableAdapters.ResumeNameDetailsTableAdapter" UpdateMethod="Update"> <DeleteParameters> <asp:Parameter Name="NameDetailID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="NameDetailID" Type="Int32" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="GridViewResumeNameDetails" PropertyName="SelectedValue" Name="NameDetailID" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:ControlParameter ControlID="HiddenFieldGUID" Name="UserID" PropertyName="Value" Type="String" /> </InsertParameters></asp:ObjectDataSource> Below is the partial of my Form View: <asp:FormView ID="FormViewResumeNameDetails" runat="server" Caption="<br />" CaptionAlign="Top" CssClass="formView" DataKeyNames="NameDetailID" DataSourceID="ObjectDataSourceResumeNameDetailsFV" EnableViewState="False" HeaderText="Name Details" HorizontalAlign="Center" OnItemDeleted="FormViewResumeNameDetails_ItemDeleted" OnItemInserted="FormViewResumeNameDetails_ItemInserted" OnItemUpdated="FormViewResumeNameDetails_ItemUpdated" RowStyle-HorizontalAlign="Center" SkinID="FormViewStandard" Width="100%"> Below is my insertion C# code: protected void FormViewResumeNameDetails_ItemInserted(object sender, FormViewInsertedEventArgs e){ GridViewResumeNameDetails.DataBind(); FormViewResumeNameDetails.DataBind(); GridViewResumeNameDetails.Visible = false; FormViewResumeNameDetails.Visible = true; } Any help in writing th C# code to call the stored procedure to call the scope_identity would be appreciated, as this has me stumped, even though I am sure it is quite a simple fix.
View 4 Replies
View Related
Mar 10, 2008
Hi,please help me, i have a data driven site,i need to display a table who look like this Name Total Used RemainingA 10 5 5B 20 13 7C 12 12 if you would look on the field of total, used and remaining, they look like an excel formula which is total - used = remaining or vise versa.but the problem in this table is the database, it look like thisname On/OffA 1A 1A 0and soon....... B 1B 0B 1and soon.... until C so how do i count the total, is the number of A's or B's or C's in the whole database then Group By the 'name' fieldthen how do i count the used is the number of ON in the On/Off field while remaining is counting the Off,so my question is i have RUN this using 3 different gridview each has its own datasource, that look like this for the used select name, count(on/off)
from myTable
where on/off = 1
group by name for the remaining select name, count(on/off)
from myTable
where on/off = 0
group by name and for the total, just used the same Select but without the where clause,i get this problem when the field "used" (please refer from the 1st table) it has a blank value, which is because my data base show that in deed name "C" has not been used, but eve though it should show '0' instead of a blank, so how can i do that???please help me, thanks
View 6 Replies
View Related
Sep 25, 2015
I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
View 3 Replies
View Related
Sep 18, 2015
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
View 2 Replies
View Related
Jul 22, 2014
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ
View 7 Replies
View Related
Feb 22, 2006
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors:
1. The wizard hangs for several minutes before displaying the columns from that table.
2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed.
I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
View 2 Replies
View Related
Oct 2, 2007
Hello everyone, I have a table in which I need to iterate field, possibly several rows, when I enter a new record with the same item ID number. An example will make this much clearer.
ItemID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0
I dont want to delete any old rows so that I can keep a history of where each item has been. The iterative column is in reverse order so that 0 is the newest value (location) and higher numbers are older locations. An item could go through a CurrentLocation several times.
Now, if I insert a row with ItemID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
What would be the easiest, best way to do this? Use a stored procedure or do it in my code or what? I'm pretty new at SQL server so if i'm missing a better way to accomplish the same thing, then please point me in that direction. Thanks for your help and/or time.
Scott
View 3 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Feb 26, 2014
We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular value get inserted in a tag.
For ex:
<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">
In the above xml data if we have the value 21545 in Stab the trigger needs to be executed and mail needs to be sent to a distribution list.
The Trigger should not be initiated if value other than 21545 is updated...
View 4 Replies
View Related
Jul 20, 2005
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies
View Related
Aug 4, 2015
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV)
1X
1Y
Results Table(SQL)
CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
2015-08-04 08:35:29.203 successfully inserted value 1Y
2015-08-04 08:30:29.203 Test Pass
2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message
Example:
Results Table CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y
View 3 Replies
View Related
May 19, 2014
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
View 4 Replies
View Related
Aug 17, 2015
I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)
TableA;
ID(identity) Date (NOT NULL Defaulyt Getdate() )
Name Phone
1 2014-06-17 17:28:21.190
Nick 12345678910
2 2014-05-17 17:28:21.190
Stan 00045678910
3 2015-08-17 17:28:21.190
Kim 11111678910
4 2015-08-17 17:28:21.190
Tom NUll
3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.
Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....
the reason is ,
Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted with exact date and time.
Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after 1 day or after 1 week ,but i want only rows that are inserted )
I tried with using where Datecreated, but did work.
View 4 Replies
View Related
Oct 13, 2006
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies
View Related
Jul 11, 2006
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
View 16 Replies
View Related
Feb 1, 2008
Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
View 1 Replies
View Related
Jul 23, 2005
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies
View Related
Nov 3, 2005
Trying to get my head around this table:
I thought it contained all data used in an insert; meaning if i enter a row in a table the inserted table should then contain all data entered?
Ive created multiple tables linked via primary key Learner_ID this key is unique to any/all students and can never be duplicated hence i thought i would use it to link the tables.
I have tables Fab / Main / Elec / Learner_info / WS_WR.
For Fab / Main / Elec the only data i need form Learner_info is the Learner_ID field, i simply used:
INSERT INTO FAB(Leaner_ID)
SELECT Learner_ID
FROM Inserted
as a trigger to insert the ID into these tables. Everything works perfectly fine. So i assumed that i would be able to use something very similar to insert say:
Learner_ID, FNAME, LNAME, ONAME and C_NUM from the learner_info table into the WR_WS table. Apparently i was wrong I can insert the learner_ID perfectly but if i then try to add another insert trigger eg: insert fname i get an sql error:
Cannot insert the value NULL into the column 'LEARNER_ID', table 'swdt_im.WS_WR'; column does not allow nulls. INSERT fails.
Now from reading the error it suggests to me that because the second trigger is running my learner_id value has become a NULL. Obviously a primary key wont accept this value so the statement fails.
What im struggling to understand, im sure i will solve the problem eventually but i would apreciate help, and this is most important to me rather than a solution is this:
If i enter data into a row on a table does that data go into the inserted table in the same row? or does the data go into inserted as a single entry row, meaning when i tab along from learner_ID into FNAME does Learner_ID become row 1 (in inserted) and FNAME the current row? or do they go into the appropriate columns?
I was under the assumption that i could enter:
LEARNER_ID / FNAME / LNAME / ONAME / C_NUM
into one table and have a trigger insert those values into multiple other tables. Dont suppose someone can help me out im alright querying a SQL db but ive never had to develop anything other than a single table one myself before
Thanks in advance
View 1 Replies
View Related
Mar 4, 2004
Hi,
I am currently working on a MS SQL server 2000.
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this?
Thanks
View 1 Replies
View Related
Aug 20, 2004
I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:
CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals]
AFTER INSERT
AS
Declare @Portal int
set @Portal = (select PortalId FROM inserted)
Declare @TabId Int
set @Tabid = (select TabID from Tabs where Tabs.PortalID = @Portal
and Tabs.TabName = 'MyTab')
Declare @ModuleId int
set @ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @TabId
and Modules.ModuleTitle = 'MyModule')
update Modules
set
AuthorizedEditRoles = '-1;'
where ModuleId = @ModuleId
View 4 Replies
View Related
Apr 19, 2006
Hello,
I have 2 tables, and use objectdatasource and stored procedures, with sql server.
Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table.
How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one.
Thanks for your help.
View 3 Replies
View Related
May 31, 2006
Does @@ROWCOUNT always return an accurate acount of rows affected by last query OR can it be equal to zero when some rows have been affected?
View 2 Replies
View Related
Feb 17, 2005
hi, i am writing a trigger to log inserts,updates and deletes in a table and would like to also enter the user details ie who did the transaction. is the spid in the inserted table? if not how do i get this information?
TIA
View 3 Replies
View Related
Nov 21, 2007
Hi all,
I am inserting one row from sp which is at one database into table on other database.I want to retrieve one columns value which will be recently inserted .
I was trying it using output parameter and @@Identity in sp.
But hardly any success.I guess we can not get @@Identity values from other server.
Can anybody suggest better way to do it
Thanks
Kedar
View 7 Replies
View Related
Mar 10, 2004
Hi,
Now thanks to you good folks on here, I have recently found out that when inserting data into table, there is a system table which can be queried with triggers - specifically called "Inserted".
What I am wondering is what are the limitations of what I can do with the data in this table?
I know I can query it from within the trigger, but can I update data specifically in this table before it is inserted?
(ie IF field1 FROM inserted = 'blah' UPDATE inserted SET field2 = 'something')
If so is there anything that I need to look out for? Concerns? Etc?
Thanks in advance for your help
Cheers
View 7 Replies
View Related
Sep 10, 2014
Data is not getting insert into table. Below mentioned is the query used. I need getting the data inserted to the table
DECLARE @FilterTable Table
(
ColumnName varchar(50),
Value varchar(250),
Type varchar(50)
[code]....
View 1 Replies
View Related
Aug 24, 2007
hi
for after trigger the records stored in followig table
inserted and deleted table.
but i want to know where this tables physically stored ...i mean in which database master or some other database?
and 2nd thing tigger fired for each row or for only insert,delete,update statement?
thanx
View 8 Replies
View Related
Aug 2, 2007
Hi,
I am building my after update trigger. The code snippet looks like this:
DECLARE Curs_PrimKey CURSOR FORSELECT * FROM Temp_PrimKeyOPEN Curs_PrimKeyFETCH NEXT FROM Curs_PrimKey INTO @P_KeyWHILE @@FETCH_STATUS = 0BEGIN EXEC('INSERT INTO Temp_Value SELECT '+ @P_Key + ' FROM inserted') SELECT @P_Key_Value = (SELECT PValue FROM Temp_Value) EXEC('DELETE FROM Temp_Value') SELECT @P_Key_Insert = @P_Key_Insert + @P_Key + ' = ' + @P_Key_Value + ', ' FETCH NEXT FROM Curs_PrimKey INTO @P_KeyENDCLOSE Curs_PrimKeyDEALLOCATE Curs_PrimKey
You must know that in the trigger after update there is a special table called inserted. From the code above you can see that I am trying to loop all primary key which is included in Temp_PrimKey. Based on the key I want to select the value from table inserted. If I update a row in the table it gives error:
"No row was updated.
The data in row 13 was not committed.Error Source: .Net SQLClient Data Provider.Error Message: Invalid object name 'inserted'."
I am guessing that if we EXEC the query it is not execute inside the trigger environment, hence it doesn't find the table 'inserted'. Can anybody tell me how to work around this? Thanks.
View 3 Replies
View Related
Jun 5, 2006
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies
View Related
Apr 11, 2007
hi ,
In my database I have table with column of datatype nvarchar in which I am writing the address of users.Now address field in aspx page is multiline because of which my address contains characters and
. These special characters are getting inserted in database as square boxes. Is their any way to avoid it. Also the constraint is that I don't want to replace or
by space before writing contents to database.
Please write to me any solutions u are having.
thanks in advance.
Thanks and regards,
Nita Jadhav.
View 1 Replies
View Related
Jul 27, 2007
There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row at exactly the same time?
View 1 Replies
View Related
