Writing Trigger To Insert Records Into Master And Child Table At A Time ?
Oct 17, 2007
I am developing an application in vb.net 2005 using SQL Server 2000.
In this I have two tables SessionMaster and SessionChild.
Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type,
Fields of session child - SessionChildId, SessionMastId, UserName, Comment.
SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields.
Please how to write trigger to insert record into both tables at a time.
View 2 Replies
ADVERTISEMENT
May 15, 2007
I have a situation here. Please
advice me on this.
I have a master table and a child
table. They have a PK and FK relationship. The master table has an identity
column with auto increment set to true. This map as a FK in the child table. My
questions are:
Can I have a single form to insert a new record into
master and child table at the same time?This has to be accomplished without stored
procedures. Can it be done?Is it possible to do this with a single insert query?
If yes, can it be done with sql data source or
dataset or tableadapters’?Please point me towards appropriate link for doing
so.
Thank you.
I am currently using SQL Server
2005 and VS 2005.
View 1 Replies
View Related
Mar 19, 2007
I have a set of parent/child records that need to be exported to a space delimited Flat File. Each parent record must be followed by 3 child records, each on their own line with different format.
I have a prototype using the Derived Column component that concatinates the various fields of each record into one "wide" text column. This fools SSIS to think that each row has the same format. Then I merge them together using an artificial sort id. But this seems overly tedious and very brittle.
What would be the best approach to writing these records out? I'm hoping there is a better more maintainable method.
Thanks,
Jon
View 4 Replies
View Related
Feb 4, 2015
I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
View 6 Replies
View Related
Mar 14, 2015
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
View 1 Replies
View Related
Nov 5, 2004
Hi,
I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?
Thanks a lot!
View 1 Replies
View Related
Dec 23, 2011
we have various user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)
This field is a drop down list , after insert committed , i want other user defined field to populate.
Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be populated.
CREATE TRIGGER User_Defined_field_KAE
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER
INSERT
AS
INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Type_Id,
name
SELECT
Client_Id,
'1089',
A.USER_PHONE_1
FROM inserted , [dbo].[ADMN_User_Details] as A
WHERE name = a.user_id;
View 11 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
May 12, 2015
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from  Master table to parent and child table i am using using expresssion likeÂ
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ONÂ
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER ANDÂ
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) Â and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.
If i run this expression to remote server  i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.
View 10 Replies
View Related
May 9, 2006
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.
Thanks, James
View 1 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).Â
View 3 Replies
View Related
Apr 28, 2006
Hello,
We have a complex functionality of migrating data from a single record into multiple parent child tables.
To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.
Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.
As of now, we are using the following approach for achieving this functionality.
1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.
2. Use the recordset as the source to a foreachloop , using foreachADO enumerator
3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table
In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)
Thanks,
Satya
View 3 Replies
View Related
May 17, 2006
Hi...how can I write a dynamic sql statement to insert x number of child records? Thx in advance
View 4 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
Mar 17, 2008
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
View 1 Replies
View Related
Mar 13, 2008
Hi
i have a table named categorymaster
categoryid catname parentid
1 Boxing 0
2 Tennis 0
3 Basketball 0
4 MayWeather 1
5 Tyson 1
6 Clinton woods 1
7 RogerFederer 2
8 Micheal 3
9 Hingis 2
so if i give input say categoryid=1[This falls under main category-boxing]
i need to get result as
1 boxing [main category]
4 mayweather [sub category]
5 tyson [sub category]
6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson]
result should be as
1 boxing [main category]
5 tyson [sub category]
hope u can get my question
Thanks in advance
View 2 Replies
View Related
Jun 13, 2007
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
View 7 Replies
View Related
Aug 5, 2007
Hi,
I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
Please advice.
View 7 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
May 24, 2013
I am trying to update a parent table with a summation of its child records. The child records are being deleted because the transaction has become invalid because payment was made with a bad check or there was a posting error. So a rollback of sorts is required.
Here are is the DDL for the tables and DML for the data:
Code:
DECLARE @t1 TABLE
(
[Year] int NOT NULL,
[Parcel] varchar(13) NOT NULL,
[InterestDateTime] datetime NULL,
[Principal] decimal(12, 2) NULL,
[Penalty] decimal(12, 2) NULL,
[Code] ....
I tried to use a Merge statement with an ON MATCH for each TransType, but it complained that I could not have multiple update statements. OK. So I tried a MERGE with single update statement with a case and it complained that I was updating the same parent multiple times, which I was and want to! So, I tried the following update statement and it still does not work, though no error message.
Code:
update t1 set
t1.Principal = t1.Principal + (case when t2.TransType = 'R' then t2.Payment else 0 end),
t1.Penalty = t1.Penalty + (case when t2.TransType = 'P' then t2.Payment else 0 end),
t1.Interest = t1.Interest + (case when t2.TransType = 'I' then t2.Payment else 0 end)
from @t1 t1
inner join @t2 t2 on t2.YEAR = t1.YEAR and t2.Parcel = t1.Parcel
I am expecting the following after the update:
Code:
Select * from @t1
201200000018092013-03-14 00:00:00.000 211.15 10.00 3.14
201100000018092013-03-14 00:00:00.000 206.12 10.00 18.20
201000000018092013-03-14 00:00:00.000 219.41 10.00 35.37
200900000018092013-03-14 00:00:00.000 0.00 0.00 0.00
2012000001808X2013-03-14 00:00:00.000 9.65 0.00 0.06
2011000001808X2013-03-14 00:00:00.000 378.70 10.00 32.73
2010000001808X2013-03-14 00:00:00.000 0.00 0.00 0.00
2009000001808X2013-03-14 00:00:00.000 341.96 3.00 142.74
All I am getting are the original values.
View 14 Replies
View Related
Feb 13, 2008
Hi all,
I am sure someone must have run into this before. I have a couple of tables with a parent child relationship.
I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into.
However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship.
I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed.
However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction.
Is it possible to postpone trigger fire until after both parent and child table values have been inserted?
Thank you,
John
View 8 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Feb 24, 2015
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
View 2 Replies
View Related
Oct 16, 2015
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
View 12 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Jul 30, 2014
I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;
[code]....
View 2 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
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.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
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)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Sep 4, 2014
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
View 1 Replies
View Related
Jul 23, 2005
I use Ftp to import a comma delim file.Problem:Day 1 file has 5 records. During the course of Day 1 ,users makechanges to a field in the five records.Day 2 has same 5 records as they were at the beginning of Day 1(withoutchanges made during Day 1) plus 1 new record.I need a statement that will only add the 1 new record to the mastertable and leave the other 5 fields alone.I have a composite key that prevents duplicate addition.My original idea was to have a master table(Day 1) and a dailytable(Day 2) and do some kind of join that would give we only therecords in Day 2 that do not appear in Day 1 using the Composite key.This has not worked.Any ideas?
View 2 Replies
View Related
Jun 29, 2007
Hi Guru's,
I am facing a problem in deploying the SSIS packages.
My Scenario is like this
1. Have one Master SSIS Package.
2. Master SSIS Package inturn calls 3 Different packages using execute package task.
3. I have a common Configuration file for all these packages.
4. I am using File System deployment.
5. I have imported all the packages i.e master and the 3 child packages using SQL Server management studio.
6. Now i have created a job, and added the master package as a step.
7. I have a variable declared in the configuration file and the child packages will be getting their path from this variable.
The problem since i know the imported packages lies in "C:Program FilesMicrosoft SQL Server90DTSPackages<<Folder Name>>" i have poited the child package variable to point to this directory.
I still want to import all the packages and want to get this directory info dynamically thorugh the script so that i can assign this at run time.
I am using "Directory.GetCurrentDirectory" But it's giving me a different location when we run the master package.
Can any one has any idea or comments how to retrive the path of the master package?
Even a work around or any other option is also fine....
Regads,
Dev
View 1 Replies
View Related
Oct 1, 2007
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
View 1 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
