Creating A Trigger To Insert Values From One Table To Another
Apr 15, 2008
hello everyone ,
i have a table named "Employee" with EmpID as PK.
i want to insert EmpID one by one in another table named "AssignedComplaints"
so if i have all the EmpID inserted in "AssignedComplaints" table then on next insert operation , the first EmpID will be inserted and then second on so on.
like i gave u a sample
i have three EmpIDs in "Employee" table named M1,M2,M3
first M1 is inserted secondly M2 and lastly M3.
now i have all EmpID in "AssignedCompalints" table.
now if i do want to insert again then the whole process will repeat , first M1 will be inserted secondly M2 and so on.
i need the query
i have created a trigger and will use this query in that trigger.
thanks
View 11 Replies
ADVERTISEMENT
Jan 14, 2008
Hi,
i am facing a problem with creating a Trigger to insert into another table.
i have 4 tables namely:
PurchaseOrder
PurchaseOrderItem
DeliveryOrder
DeliveryOrderItem
i want the trigger to create a new row in the DeliveryOrder when i creates a PurchaseOrder.
I tried the following:
CREATE TRIGGER trgInsertPO
ON PurchaseOrder
FOR INSERT
AS
INSERT INTO DeliveryOrder (DeliveryOrderNo,DeliveryOrderDate, SupplierID, DeliveryOrderStatus)
VALUES (PurchaseOrderNo,PurchaseOrderDate,SupplierID,'d')
GO
but it cant work. Help required.
Thanks.
View 6 Replies
View Related
Feb 21, 2002
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob
INsert into b values(1,1,2,2)
select 0
(this is a cut up version of the sp just to show about the way it is formated)
Please help.
View 1 Replies
View Related
Nov 7, 2006
Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
View 2 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jun 5, 2004
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
View 6 Replies
View Related
Feb 1, 2008
Hi,
When a record is to be inserted, how can I check whether the insert is succeeded or not?
CREATE TRIGGER MY_TRIGGER ON MyTable AFTER INSERT
AS
DECLARE @id INT
DECLARE @name NVARCHAR(50)
SELECT @id=id,@name=name FROM INSERTED
BEGIN
INSERT INTO MySubTable(id,name) VALUES(@id,@name)
-- if failed, rollback
END
GO
GO
View 3 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 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
Feb 10, 2004
I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:
---------------------------------
CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
FOR INSERT, UPDATE
AS
declare @currency as char(50)
declare @country as char(50)
declare cur cursor for SELECT currency, country
FROMinserted
OPEN cur
fetch cur into @currency, @country
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
begin
set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
CLOSE cur
DEALLOCATE cur
RAISERROR (@currency,16,-1) with log
return
end
if not exists(select * from listinfoid where listname = 'country' and listid = @country)
begin
set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
CLOSE cur
DEALLOCATE cur
RAISERROR (@country,16,-1) with log
return
end
else
fetch cur into @currency, @country
END
CLOSE cur
DEALLOCATE cur
update Client set currency = UPPER(currency), country = UPPER(country)
---------------------------------
I use a cursor to handle multiple rows in an update query.
(SQL2000-server)
Is there an easier och better way to do this?
I´m a bit unsure of this code.
Thanx!
/Erik
View 2 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
May 15, 2008
I want to create this table:
create table new_price (
new_price_item_id VARCHAR(25),
new_price_item_lst_lnd_cost NUMERIC(7,2),
new_price_item_prc_2 NUMERIC(7,2) NOTNULL)
values (
"000001010045""$127.05""$329.99"
"000001031565""$147.38""$379.99"
"000001050014""$117.61""$299.99"
"000001050023""$274.45""$679.99"
"000001050035""$296.30""$749.99")
Is this the proper SYNTAX to insert multiple rows of values or is there something I missed to differentiate between each row of values?
Brooks C. Davis
IT AdministratorLogistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9
View 10 Replies
View Related
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
Apr 2, 2007
Hi,
I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.
Do I create the triggers to maintain indexes myself?
Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?
Would it be "Alter index <index name> REBUILD command?
Appreciate the help.
Gulden
View 6 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Oct 29, 2007
I have a table that displays a bunch of data based on their percentage changes.
There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.
I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?
Thanks,
Steven
View 3 Replies
View Related
Jun 10, 2015
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
View 0 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
Mar 11, 2004
I am new to triggers and need help on the following:
I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row.
Thanks for any suggestions...
Alan
View 4 Replies
View Related
Mar 15, 2008
hi need help how to do this
TRIGGER on INSERT INTO SELECT FROM table + add one row for evry employee (on insert)
this is table tb_test1
empid fname unit fld1 fld2 fld3 fld4 fld5
----------------------------------------------------------------------------------------
111 aaa 1 a b c d d
222 bbb 3 a c e g g
333 cccc 5 s h t u j
INSERT INTO [nili].[dbo].[tb_test2]
([empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5])
SELECT [empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5]
FROM [nili].[dbo].[tb_test1]
i need to insert into tb_test2 + add TRIGGER
ADD one row for evry employee
val_orginal=1 (the orginal ROW)
val_orginal=2 (the extra ROW)
this is table tb_test2
after the insert
HOW TO THIS
empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal
-------------------------------------------------------------------------------------------------
111 aaa 1 a b c d d 1
111 aaa 1 - - - - - 2
222 bbb 3 a c e g g 1
222 bbb 3 - - - - - 2
333 cccc 5 s h t u j 1
333 cccc 5 - - - - - 2
and tnx for the help
View 1 Replies
View Related
Jul 23, 2005
I currently have 2 tables as follows:CREATE TABLE [CRPDTA].[F55MRKT119](mhan8 int,mhac02 varchar(5),mhmot varchar(5),mhupmj int)GOCREATE TABLE [CRPDTA].[F55MRKT11](mdan8 int,mdac02 varchar(5),mdmot varchar(5),mdmail int,mdmag int,mdupmj int)What I would like to do is place a trigger on F55MRKT119 which willinsert records to the F55MRKT11 if they do not exist in that tablebased on the [mdan8] field. If the record does exist I would likeUpdate the corresponding record and increment either the [MDMAIL] orthe [MDMAG] based on the inserted [MHMOT]. What I have so far is asfollows:TRIGGER #1:CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]FOR INSERTASBEGININSERT INTO CRPDTA.F55MRKT11select INS.MHAN8, INS.MHAC02, INS.MHMOT,case when INS.MHMOT='MAG' then 0 ELSE 1 end,case when INS.MHMOT='MAG' then 1 ELSE 0 end,'0' from INSERTED INSWHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)ENDTRIGGER #2:CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]FOR UpdateASBEGINUPDATE CRPDTA.F55MRKT11SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAILwhen INS.MHMOT<>'MAG' then 1+MDMAIL end,MDMAG= case when INS.MHMOT='MAG' then 1+MDMAGwhen INS.MHMOT<>'MAG' then 0+MDMAG endfrom INSERTED INS JOIN CRPDTA.F55MRKT11on(ins.mhan8=mdan8)ENDFor instance if I do the following insert:INSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','AL4','0')thenINSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','MAG','0')This is what I expect in both tables:[CRPDTA.F55MRKT119] (2 Records)MHAN8 MHAC02 MHMOT MHUPMJ------ ------ ----- ------212131 VK4 AL4 0212131 VK4 MAG 0[CRPDTA.F55MRKT11] (1 Record)MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ----- ------ ----- ------ ----- ------212131 VK4 AL4 1 1 0The insert part works fine in that it iserts in both tables with thecorrect values. However it seems as if the Update protion is failingfor some reason. WHat I have tried so far is setting the trigger orderfor the update to run first and vice-versa, but still no luck. Anyhelp would be appreciated.
View 1 Replies
View Related
Jul 23, 2005
Hi,Does anyone know of a simple way to do this? I want to create aninsert trigger for a table and if the record already exists based onsome criteria, I want to update the table with the values that arepassed in via the insert trigger without having to use all the 'set'statements for each field (so if we add fields in the future I won'thave to update the trigger). In other words, I want the trigger codeto look something like this:if exists (select * from TableA where Fld1 = inserted.Fld1) then//don't do insert, do an update instead (would i want to rollback here?and will I have access to the 'inserted' table still?)Update TableASet TableA.<all the fields> = Inserted.<all the fields>where Fld1 = inserted.Fld1end ifAny help or ideas would be appreciated.Thanks,Teresa
View 3 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
Mar 7, 2008
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I need a SQL Script to accomplish this. thanks
View 6 Replies
View Related
Apr 14, 2004
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?
I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.
thanks!
View 4 Replies
View Related
May 16, 2006
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
View 6 Replies
View Related
Sep 24, 2014
I have a table as dbo.Bewegungen and it will be updated every day with new dates
I inserted once the values of this table to another table with group by. I want to know how should i insert the new values every day to the new table? i don't want to insert the previous values again.
it has 5 columns as primary key and one of them is Date.
View 2 Replies
View Related
Jul 12, 2005
I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
any ideas?
View 1 Replies
View Related
Dec 4, 2006
Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
View 3 Replies
View Related
Mar 17, 2008
What is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)?
E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
thank you very much.
View 3 Replies
View Related
Jun 2, 2004
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
View 2 Replies
View Related
