Trigger Between Two Tables In Separate Db
Mar 3, 2001
I am new to T-SQL and triggers
Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert
AS
Update customer SET cust_id =( SELECT
Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id))))
+ Convert (varchar(10),i.id)
+ Substring(i.lastname,1,3)
+ Substring(i.firstname,1,1)
from employee C INNER JOIN inserted i on i.id=c.id)
from employee C INNER JOIN inserted i on i.id=c.id
Saad
View 2 Replies
ADVERTISEMENT
Feb 15, 2012
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies
View Related
Nov 4, 2015
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:--
CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVERÂ
AFTER CREATE_DATABASE
ASÂ
--execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
View 5 Replies
View Related
Apr 29, 2014
I have two tables
Books
-BookID
-CustomerID
Magazines
-MagazineID
-CustomerID
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
CustomerID, BookCount, MagazineCount
View 3 Replies
View Related
Jan 5, 2001
We have some tables that we have spread across two databases. The segregation isn’t essential, but the entities involved were disparate enough that we thought it made sense. However, our client app regularly & frequently requires information that can only be answered by queries to tables in both databases. It has been suggested that segregating the tables as we have introduces a performance hit. At this stage, it would be relatively easy to re-combine the tables into one DB.
View 1 Replies
View Related
Oct 31, 2007
Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P.
View 8 Replies
View Related
Apr 7, 2008
I've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks.
What does this mean exactly and how would you configure this?
Is this a common practice in high-performance real-world environments (or should it be)?
View 3 Replies
View Related
Jan 28, 2005
I would like to 'one table' record to separate 'two or three tables'
. I just know use the DTS , try to import and export again and agian.
So trouble.
Could you give me some suggestions for me? For example ,
'Cursor' write in new table . But I try to SQL Server Books Online
which is not suitable for me solving problems. One table separate two
or three tables. Can you wirte the detail example for me?
Thx a lot.
View 1 Replies
View Related
Sep 7, 2005
Hi,
We are building an application for online system for people to place ADs for selling various used items like Car, Electronics, Houses, Books etc.
If someone selling a car then he can fill out headline, year, make, model, mileage, transmission, condition, color, price, description, contact etc.
Similarly if someone selling a digital camera he will fillout headline, memory, zoom, megapixel, maker, model, color, batter, description etc.
Option 1: I can have a main table to hold the common attributes of all different types of ADs (headline, images, contact, price, color, condition, description)
+ 1 table to store string values of all ADs (car: maker, model, square feet (if house), memory, megapixel (camera) etc)
+ 1 table to store the droplist select values(car: transmission, door, seat etc; house: year_built)
pros: single table for all ADs. unique IDs for all ADs, easy to extend as new attributes can be dropped easily.
cons: lot of physical reads of 2nd and 3rd table from join. 10 times physical reads compared to option 2 when reading 5000 records.
Option 2: have different set of table for each AD type. Car will have its own main table + 1 table to store multiselect list box values.
Similarly housing will have its own set of tables
pros: 10% less physical read than option 1.
cons: hard to add new attributes. We have to modify the main table by adding one column.
Query will go to different table based on the category.
Do you have any suggestions on which way to go?Thanks
View 2 Replies
View Related
Jul 30, 2014
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
View 9 Replies
View Related
Aug 20, 2014
I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.
My end result should look like the below example.
Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0
So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.
Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.
--create the temp table
Create table #SalesLine
(
Novarchar (50) not null
, LocationCodevarchar (50) not null
, QtyCommitint not null
)
create table #ItemLedgerEntry
[code]....
I am close to the desired results but can't find a way.
View 9 Replies
View Related
Jul 20, 2005
I have a report that's created each day as a flat textfile.Because I came from the Access world, I created a macro that importsit with a schema that gives meaningful names to the various columns,and then uses a query to massage some of the data for me (deletes thefirst blank row and does a couple of calculations)Then I use DTS to import the Access query as a table.the textfile has a column called "File_num", and among several others,a column called "Serial_num". (the file numbers represent shipments,and sometimes there are more than one serial number in the shipment,etc., so there is a separate line for every serial number)Naturally, I would like to split this info into two tables..one thatdoes not contain the serial numbers and has a primary key on the"File_num" column, and another table that would contain just the"File_num" and "Serial_num" columns. That way I could relate themlater...but most importantly, it will give me a table where I can usethe "File_num" as my primary key.What would be the best way to import these two tables from one sourcetextfile? The other thing that gives me problems is that the textfile has no column names, and the first row is always blank.I'm very new to SQL and DTS and would appreciate any direction.Thanks,Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
View 1 Replies
View Related
Apr 17, 2008
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
-El Salsero
View 5 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Apr 29, 2015
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
View 6 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Feb 10, 2007
How can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.
thanks.
For example:
a: data
b: data
c: data
a: data
b: data
c: data
View 6 Replies
View Related
May 16, 2008
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
View 7 Replies
View Related
Aug 6, 2007
Hi
I am trying to create a trigger, that sets default values for all clients except one specific client in my table, however, the problem that i'm having is that when a member gets inserted into the database then the MemberID that gets created for that member in the Members table must also be inserted into the MemberUsergroup table
Here is the code that i have so far, It seems to work perfectly for the members table but because of the Foreign key constraint on the MemberUsergroup table it obviously doesn't work perfectly yet.
Any Help will be greatly appreciated.
Code Snippet
ALTER Trigger [csp_MemberInsertRegister]
ON [dbo].[Members]
FOR INSERT
AS
UPDATE Members
SET Registered = 1
where clientid <> 94
AND MemberID = @@Identity
Kind Regards
Carel Greaves
View 3 Replies
View Related
Feb 15, 2001
I need to keep track of changes (delete/modify) in sysusers table. SQL Books Online mentionend that SQL Server 7.0 doesn't support trigger on system tables. What options do I have?
LT
View 2 Replies
View Related
May 2, 2007
I'm using SQL 2000, which is integrated with a VB.NET 2003 app.I have an Audit trigger that logs changes to tables. I want to applythis trigger to many different tables. It's the same trigger, with theexception of the table name. I could manually change the table name inthe trigger and create it, over and over, but I'd like to automatethis - by iterating through the collection of tables and passing thetablename to something that would create the audit trigger for thattable.Any ideas? With any scripting language I can create the text of theCREATE TRIGGER procedure for each table and use simple textreplacement, but how do I automate that inside of SQL?Thanks.
View 9 Replies
View Related
Mar 29, 2001
SQL 7 SP2
Are the tables inserted and deleted available from within a sp which is called from a trigger ?
Craig
View 1 Replies
View Related
Aug 2, 2001
I understand that triggers are not allowed against system tables for SQL 7.
Does anyone know if there are third-party solutions for setting triggers against system tables?
Or how one may possibly detect changes against system table without running a scheduled job?
Our requirement dictates that we detect real-time changes to system tables, for example, whenever a new table is created, or column name changed.
View 2 Replies
View Related
Oct 14, 2015
How to set ddl triggers for specific tables, instead of on entire database.
View 2 Replies
View Related
Dec 12, 2005
I have two databases one running of MS SQL Server and other on Oracle..
My requirement is such that, I want an trigger to be written on table in MS SQL Server such that it inserts / updates values of tables in Oracle Database tooo..is there any methodology for the same..
regards.
View 5 Replies
View Related
Jul 23, 2005
I have written a trigger that's supposed to go out and deletecorresponding records from multiple tables once I delete a specificrecord from a table called tblAdmissions.This does not work and I'm not sure why...Here's the code that's supposed to run, let's say, if a user (via a VB6.0 interface) decides to delete a record. If the record in thetblAdmissions table has the primary key (AdmissionID) of "123", thenthe code below is supposed to search other tables that have relatedinformation in them and also have an AdmissionID of "123" and deletethat information as well.Any ideas? Here's the code:CREATE TRIGGER tr_DeleteAdmissionRelatedInfo-- and here is the table nameON tblAdmissions-- the operation type goes hereFOR DELETEAS-- I just need one variable this timeDECLARE @AdmissionID int-- Now I'll make use of the deleted virtual tableSELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)-- And now I'll use that value to delete the data in-- the tblASIFollowUp TableDELETE FROM tblASIFollowUpWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblProgramDischarge TableDELETE FROM tblProgramDischargeWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblRoomAssignment TableDELETE FROM tblRoomAssignmentWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblTOADS TableDELETE FROM tblTOADSWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblUnitedWaySurvey TableDELETE FROM tblUnitedWaySurveyWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblWFGMSurvey TableDELETE FROM tblWFGMSurveyWHERE AdmissionID = @AdmissionID
View 4 Replies
View Related
Nov 9, 2007
I dont know what I am doing wrong. The trigger (see below) is doing what I want it to do when I do this:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' , 'Boss')
but when I remove one value, the result in the logtable is NULL:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' )
How can I change the trigger so that it will give me the information of the values that have been updated, inserted or deleted when I dontchange all values (just a couple of them)?
My trigger:
CREATE Trigger trigex
ON dbo.personal
FOR insert, update,delete
AS
INSERT INTO logtable (Innan_värde, Ny_värde)
SELECT
rtrim(cast(d.personal_id as varchar)+', '+cast(d.chef_id as varchar)+', '+rtrim(d.efternamn)+', '+ rtrim(d.fornamn)+ ', '+ rtrim(d.titel)),
(cast(i.personal_id as varchar)+', '+cast(i.chef_id as varchar)+', '+rtrim(i.efternamn)+', '+ rtrim(i.fornamn)+ ' '+ rtrim(i.titel))
FROM inserted i full join deleted d on i.personal_id = d.personal_id
My table:
CREATE Table logtable
(Innan_värde varbinary(max),
Ny_värde varbinary(max))
Thank you !
View 1 Replies
View Related
Oct 13, 2013
I need creating an update trigger that involved two tables and a few fields.
tblCases
Fields
Defendent1
Defendent2
Defendant3
tblCaseBillingDetails
Fields
DefCount
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
View 1 Replies
View Related
Nov 29, 2007
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
View 1 Replies
View Related
Nov 4, 2015
I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
View 5 Replies
View Related
Apr 10, 2007
We have setup a replication in SQL2000:
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
View 8 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
Nov 9, 2005
Hi to all of you,
I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.
Help is highly appreciated
View 4 Replies
View Related