Triggers And Record Data Question

Jun 13, 2006

Hello,

I have a need to do something in a trigger, I have read all the in's and out's of when to use or not to use a trigger, so I am not looking for an answer like that.

What i am wondering is how to get the fields from a record that triggered the trigger.

for example

an on insert trigger......

Inserted record example:
Field names: key field1 field2 field 2
field data 1 Jack filed baseball

i want to do a trigger that will insert the differnt fields into differnt tables

on insert (very rough example)
insert field1 into names
insert field2 into status
insert field3 into sports

++++++++++++++

or better yet is it possiable to send var's to a stored procedure for example:

create procedure [insert data]

(@ID [int],
@field1 [nvarchar](10),
@field2 [nvarchar](10),
@field3 [nvarchar](10))
as
insert @field1 into names
insert @field2 into status
insert @field3 into sports

++++++++++++++


ON record insert have a trigger send the data for the record that was inserted to the stored procedure.



Hope this makes sence:
Tdar







View 8 Replies


ADVERTISEMENT

Triggers To Monitor Data Changes

Apr 6, 2001

we need to report on what changes were made to one particular column of a table. We need to report the old value, the new value and the date and userid (these are also fields in the table).
I assume we use an update trigger with a query to capture the old and new values in a temp table?
Does anyone have any sample syntax for such a situation?

View 2 Replies View Related

Triggers For Different Data Base

Jan 24, 2007

Vipin writes "Hello

Can it be possible that triggers can be fired to affect tables lying in different database.

Database may be on same Machine or may be on different machine.

Explain with Example"

View 2 Replies View Related

Triggers - Insert Data To Another Table

May 3, 2006

How do I set up an insert trigger to copy all of the inserted data to another table? In other words, when someone adds a new paramater in the params table, I want to automatically create a matching set of data in the goals table. Thanks,Krista

View 2 Replies View Related

Inserting Data Into Tables Having Triggers...

Jul 20, 2005

When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah

View 1 Replies View Related

Import Data To Table With Foreign Key Triggers

Oct 3, 2007

I've imported data from an Excel spreadsheet to a table that has fields to match the destination table I'm trying to populate. The destination table has an Insert trigger with several checks on certain fields to make sure they have corresponding records in other tables.

If I do a statement like
"INSERT INTO destinationTable
(
ItemId,
Product,
SuperID,
etc etc
)
SELECT * FROM oldtable"
it runs for a while then gives me error messages from the trigger and rolls back the Insert.

The trigger has code such as
"IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.Product Is Not Null))) != (SELECT COUNT(*) FROM tblInProduct, inserted WHERE (tblInProduct.Product = inserted.Product))
BEGIN
(Error message code goes here)
END"

So, do I need to do an INNER JOIN to each of the related files?
When I try that, I get this error:
"Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
Is SQL counting the foreign key fields as separate fields, or what?

View 6 Replies View Related

Multiple Triggers On A Table Or Encapsulated Triggers

May 12, 2008

This isn€™t an problem as such, it€™s more of a debate.

If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.

www.handleysonline.com

View 12 Replies View Related

SQL Triggers: Transfer Data From SQL Server 2000 To Visual FoxPro DBase

Jul 23, 2005

Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett

View 1 Replies View Related

Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?

Mar 7, 2008



Hey!

DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?

View 3 Replies View Related

Getting Data In The Same Record

Jun 10, 2004

I am trying to get these three different stored procedures to execute and enter data into the same record. It works, but each sql statement creates a new record, so account info, fromterritory info and toterritory info are in their own rows. Can anyone help me figure this out??

[Code]
Sub InsertData()


dim sql1 = "insertaccounttransfermike '" & AccNumber.SelectedItem.Value & "'"
dim sql2 = "insertaccounttransfermikefrom '" & FromName.SelectedItem.Value & "'"
dim sql3 = "insertaccounttransfermiketo '" & ToName.SelectedItem.Value & "'"

' Create Connection Object, Command Object, Connect to the database
Dim conn as New SQLConnection(connstr)

Dim cmd1 as New SQLCommand(sql1,conn)
Dim cmd2 as New SQLCommand(sql2,conn)
Dim cmd3 as New SQLCommand(sql3,conn)

conn.open()
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
conn.Close()

' go to the datagrid query page
Response.redirect(return_page)


[/Code]

View 4 Replies View Related

Displaying One Data Record

Jun 30, 2007

Hi all, this is a very basic question of diplaying a data. on my aspx page I have datasource that will return only ONE record.
  <asp:SqlDataSource ID="sdsCategoryName" runat="server" ConnectionString="<%$ ConnectionStrings:KaruselaConnectionString %>"        SelectCommand="SELECT Title FROM tbh_Categories WHERE CategoryID=@categoryID  ">        <SelectParameters>            <asp:QueryStringParameter DefaultValue="-1" Name="CategoryID" QueryStringField="id" Type="int32"/>        </SelectParameters>    </asp:SqlDataSource> 
on the server side I would like to manipulate the title of the page according to the data returned from the query:
 and on the behind code if (!this.IsPostBack && !string.IsNullOrEmpty(this.Request.QueryString["ID"]))
{
DataView dv2 = (DataView)sdsCategoryName.Select(arg);
this.Title = string.Format(this.Title, dv2.Table.Columns[0].ToString());
dv2.Dispose();
}
  of course it doesn't work. my question is this. do we really have to put the query datasource on the client side?and secondly, how can I view the recorsd I recieves from the query?Thanks for the help. 

View 1 Replies View Related

Data Type In Audit Record

Mar 30, 2007

Hi,I want my application to audit any data changes (update, insert,delete) made by the users. Rather than have an audit table mirroringeach user table, I'd prefer to have a generic structure which can loganything. This is what I've come up with:TABLE: audit_record*audit_record_id (uniqueidentifier, auto-assign, PK) - uniqueidenfiier of the audit recordtable_name (varchar) - name of the table where the action (insert/update/delete) was madepk_value (varchar) - primary key of the changed record. If the PKitself has changed, this will store the old value.user_id (varchar) - user who changed the recorddate (datetime) - date/time at which the change was madeaction (int) - 0, 1 or 2 (insert, update, delete)TABLE: audit_column*audit_record_id (uniqueidentifier, composite PK) - FK tocdb_audit_record table*column_name (varchar, composite PK) - name of the column with changeddatanew_value (text?) - value after the changeSo every column which changes has its new value logged individually inthe audit_column table. However, I'm not sure what data type thenew_value column should have. The obvious answer (to me) is text, asthat can handle any necessary data type with the appropriateconversion (we don't store any binary data). However, this table isgoing to grow to millions of records and I'm not sure what theperformance implications of a text column will be, particularly giventhat the actual data stored in it will almost always be tiny.Any thoughts/recommendations/criticism would be greatly appreciated.ThanksAlex

View 5 Replies View Related

Can I Add A Record Number As Data Passes Through

Oct 20, 2006

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table. 

MemberID

2898 

2899

2899

What I want it to look like when it gets to the destination is:

MemberID     RecordNumber

2898     1

2899     1

2899     2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

 

 

View 15 Replies View Related

How To Get Data From 2 Tables ...most Recent Record

Apr 25, 2008

Hello, i have this problem
tbl1 :{ pcb varchar(30) ,serial varchar(30)},result varchar(30)
tbl2: { pcb varchar(30) ,date_time varchar(30),result varchar(30),data1 varchar(30),data2 varchar(30),}

what i need is for a range of serials get the pcb (from tbl1) and for those pcb's get as resultSet the table

tbl3:{pcb,serial,data1,data2} containing most recent data on date_time column.

example. tbl1 ={ pcb1,sn1,pass}
pcb2,sn2,pass


pcb3,sn3,pass}

tbl2={pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6}

where date1 is the most recent date

Request:what i want is for serial>=s1 and serial<=s2 get

pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3



What i already did is this:




Code Snippet
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
from tbl2
left JOIN tbl1
ON tbl2.Pcb=tbl1.pcb
where tbl1.serial>='1' and tbl1.serial<='53'
and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
group by tbl2.pcb,tbl1.serial



And this works correctly.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records.I mean it would return from tbl2, all records containing pcb1,pcb2.


How can i resolve this issue?
Thank you very much,

ElectricC

View 1 Replies View Related

Updating A Record On A SQL 6.5 From Data In SQL 2000 Server

Sep 30, 2004

I need to update one row in a SQL Server 6.5 DB from a row in SQL 2000 server DB. What would be the best way to do this?


I have my 2000 server defined as a Remote Server in 6.5, however I get the error message:

contains more than the maximum number of prefixes. The maximum is 2.

View 9 Replies View Related

Trsafering Access Record In Sql Data Base...

Jan 13, 2007

Assumig i have acces my_db.md and my_mdb.mdf (in instace named DVD377-14D9E48CSQLEXPRESS)
Access mdb have table named my_table with:

field1
field2
field3

i have same table named my_table in sql instance with same named record and same property of access mdb

i want to transfer all record from access table in sql db, record by record...

similiar:

rs.recorset1 of access in rs.recorset1 of sql db
rs.recorset2 of access in rs.recorset2 of sql db
rs.recorset3 of access in rs.recorset3 of sql db


...ecc

record by record becose during the export from access to sql i make various operation on single record...

View 1 Replies View Related

SQL Server 2008 :: How To Add A Record When Data Is Not In The Table

Feb 13, 2015

I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.

create table dbo.Scott_TEST
(
YearMonth char(9),
Gender char(1),
NumOfFemale int,
NumOfMale int

[Code] ....

View 2 Replies View Related

How To Update Data Record When Defined As Money

Nov 23, 2014

When i try to use the following command;

Update (table name)
set (Field name)='0'

where ID='1449' (1449 location of the record).

The "Field name" value is 875 and i need to edit the value to 0(zero).

It appears an error -

----------------------------
Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.table name', column 'field name'. Use the CONVERT function to run this query.
-----------------------------

So if i change the data type from right click design menu to nvarchar(50) can it be edited from the command that i used above?

View 3 Replies View Related

Trsafering Access Record In Sql Data Base...

Jan 13, 2007

Assumig i have acces my_db.md and my_mdb.mdf (in instace named DVD377-14D9E48CSQLEXPRESS)

Access mdb have table named my_table with:



field1

field2

field3



i have same table named my_table in sql instance with same named record and same property of access mdb



i want to transfer all record from access table in sql db, record by record...



similiar:



rs.recorset1 of access in rs.recorset1 of sql db

rs.recorset2 of access in rs.recorset2 of sql db

rs.recorset3 of access in rs.recorset3 of sql db





...ecc



record by record becose during the export from access to sql i make various operation on single record...

View 1 Replies View Related

How To Fetch The Data Which Is Not The First Or Last Record From DB By Reporting Service?

Aug 6, 2007



I only found first or last function which means I can't show the data of others, is there any solution to solve this issue?

Thanks in advance.

View 4 Replies View Related

Updating Batch Record After Exporting Data

Aug 19, 2006

I created a package in SSIS to export data from mutiple SQL server tables to a single flat file. Once the export is completed i need to update 2 tables ( a batch table and a history table) In the batch table I need to insert a record with batch # (system generated),batch date, status( success/failed), record count( no of records in the flat file), batch filename). In the history table, i need to insert a record for each of the rows in the flat file with the foll info. batch number,datetime,status(success/failed)

My question is how do I get the batch status,record count, batchfilename for batch table update and how do i get to update the history table.

BTW, i am executing this package as a SQL Server Agent job.
I am new to Integration Services and feel lost.
Any help ?

View 8 Replies View Related

Pulling Data Back From 2 Tables - Join On Top 1 Record

Nov 7, 2013

I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.

however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)

Here's my code

SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID

WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID

[Code] ....

View 4 Replies View Related

Creating One Flat File Per Record In The Data Flow

Nov 30, 2006

I have a table that holds in each record an image (varbinary(max) actually), a text reference for the image and a MIME type for the image. I need to read this table and for each record that has been created since the last run, I need to create a file with the image as the content, the mime type as the file extension and the text reference as the file name. There will be one file created per record found by the data flow source.

I was assuming that I could use the flat file destination and manipulate the file naming using the contents of each record in the flow but am completely stumped on how to achieve this.

Does anyone have any ideas?

thanks

View 3 Replies View Related

Data Access :: Identity Column Jump1000 Record In Once

Oct 7, 2015

I have table contains more columns  and first column have ID  int not null primary key  and auto increment by 1 seed by 1 the ID 165000 record  and instant Jump to 166000 and increment by 1 ...

View 5 Replies View Related

A Recordset Obtained Via ADO Doesn&#39;t Show The First Record Using Data Report

May 30, 2001

Te first record of a Recordest obtained from a Command Object executing a Stored Procedure, doesn't show the first record when I asociate this to a data report.(VB6 - SQL7) (ADO 2.1)

If I execute the stored procedure directly from query analizer, I have obtained the right resultset.

Does anyone Knows what could be happening?

Thank You ...

View 1 Replies View Related

Createing SQL Data Base Record For Comma Delimted Records

Sep 15, 2006

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

View 6 Replies View Related

Loading Data File In SSIS With Multiple Record Layouts

Apr 4, 2007

I am trying to load a file using SSIS that contains records with two different layouts in one data file but in the flat file connection I can only specify one layout and this is causing the records with the second layout to be loaded incorrectly.



The different record layouts can be identified by the first character of the record. Example: If Field begins with "A" then assign one layout; "B" assign second layout.



Has anybody come accross this issue, if so some guidence would be appreciated.



Thanks,



ire_ssis

View 10 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table

Nov 17, 2005

We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work

Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"

Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?

View 3 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.



I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.



Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have been able to create a sample file with two of my record types.

I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.

does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?

Thanks in advance for any help you might be able to provide.


View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved