Insert Trigger To Populate Other Columns In Same Row
Sep 22, 2006
I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:
create table testtrigger(id integer unique, b integer, c integer)
go
create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go
where id is testrigger's unique id field, and c is a field derived from
b.
This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a win server 2003 standard Edition sql Server 2000 db:aaa table: a_aaa column:a_aaa_a
b win server 2003 standard Edition sql Server 2000 db:bbb table:b_bbb column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera...... Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE AS
BEGIN SET NOCOUNT ON;
DECLARE @EndDate as DATE DECLARE @CompCode as VARCHAR(15) DECLARE @PhoneGMSM as VARCHAR(10) DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA; SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111" WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
Hi all,I have two tables tbl_A and tbl_B.Table Definitions:tbl_A (field_1, field_2, field_3)tbl_B (field_1, field_2)I would like to populate tbl_B when a record is inserted into tbl_A.Note that tbl_A.field_1 = tbl_B.field_1 and tbl_A.field_2 =tbl_B.field_2 .How do I write a trigger on tbl_A to get the corresponding recordinserted into tbl_B?RegardsJune.....
Hi.I have a problem I hope someone can help me with.I have a database of events. Each event has a date and a duration(days).What I need to do is to be able to display search for events on someweb pages. My problem is that if an event is on say 15 nov 2004 andhas a duration of 3 days, at the moment if someone searches for the16th nov 2004 that event wont show up. To solve this what I have triedto do is put a trigger on my events table that populates another tableeach time a record is inserted. In the other table (orderedevents) anentry is added for each day specified in the duration field. I can thequery this table for the search. For example if this is added into theevents tableTitle: Event1Date: 15/11/2004Duration: 3Then this would be added to the orderedevents tableTitle: Event1Date: 15/11/2004Duration: 3Title: Event1Date: 16/11/2004Duration: 3Title: Event1Date: 17/11/2004Duration: 3This is what I have got so far. Someone kindly helped me with thisbefore but they had developed it on MySql, I'm using Sql Server and itdoesn't seem to work. All it does is add a blank row to OrderedEvents.Can anyone suggest any amendments that will make it work on SqlServer?CREATE Trigger TriggerInsertOrderedEventsON eventsFOR INSERTASSET NOCOUNT ONDECLARE @CountDuration int,@FixedDuration int,@eventdate datetime,@EventTitle varcharSET @CountDuration = 0SET @EventTitle = (SELECT EventTitle FROM inserted)SET @FixedDuration = (SELECT EventDuration FROM inserted)SET @EventDate = (SELECT EventDate FROM inserted)WHILE (@CountDuration <= @FixedDuration - 1)BEGININSERT OrderedEvents (Eventdate,EventDuration,EventTitle)VALUES(dateadd(day,@CountDuration,@EventDate),@Fix edDuration,@EventTitle)SET @CountDuration = @CountDuration + 1ENDSET NOCOUNT OFFThanks in advance.
Im a newbie with a sql table in a MSDE database that contains fields ofCustName, BrokerName, Type, Status.I am trying to write a trigger that does this:1. When a new record in a table is added, I would like the selectedfields to be copied to another table (call it Master).2. If that Master table already contains a record where the CustName =CustName, then simply ignore. If CustName does not match anything, thenadd the record to the Master TableIn other words, I am trying to create a table (or even a new database)that has unique records in it - making a master customer list.Any hints on how to get started on this?
Red = If ID due date was overdue, i.e. if last task completed after the ID end date(2014-06-18 00:00:00). AMBER= If any task in the ID is overdue but completed before the ID end date(2014-06-18 00:00:00) Green = If all tasks were completed on time.
I am looking for the logic to implement the AMBER for the whole ID, because the TASK_ID 3 is overdue, but completed before the ID end date (2014-06-18 00:00:00).
Basically like the topic says, I need to populate a filename with data from other columns and im having a tough time doing it.
Code:
UPDATE nice_cls_calls_0027 SET vcarchivepath = 'G:Nice Storageicestorage.safeautonet.netSafe Auto Task - Logger (807101)2006_feb_1SC_807101_'start_time'_'=stop_time'_'=channel'_'=cls_call_id'.aud'
I need start_time, stop_time, channel, and cls_call_id to populate the filename. The first 2 are datetime fields in the db and no conversion is needed for them, just raw data. The remaining two are smallint and raw data is needed there also. Any help would be greatly appreciated. TY
Hi, I have a master/detail scenario whereby to populate a formview, the user selects a row in the gridview. The SqlDataSource used to populate the formview needs to check 2 columns from the gridview, ZRef (which is the SelectedValue of the Gridview) and ZName (a string). From searching the forums it looks like I have to programmatically assign the SelectParameters in order to get ZName from the gridview. Seems fair enough, so I set the parameters in the 'Selecting' event of the SqlDataSource. (Code shown below). However the formview never shows. In debug I can see that the values I'm setting in the SelectParameters.Add are correct; and if I set the default values for those parameters in the SqlDataSource itself, everything works fine. Can anyone point me to some sample VB code that does this - I'm sure this must be a common situation. Thanks. Protected Sub sdsOff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Dim tmpSDS As SqlDataSource Dim tmpLBL As Label tmpSDS = CType(FormView1.FindControl("sdsOff"), SqlDataSource) If tmpSDS Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpLBL = Me.GridView1.Rows(GridView1.SelectedIndex).FindControl("lblName") If tmpLBL Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpSDS.SelectParameters.Clear() tmpSDS.SelectParameters.Add("ZRef", GridView1.SelectedValue) 'In debug, this shows 7 - which is correct tmpSDS.SelectParameters.Add("ZName", tmpLBL.Text) 'In debug, this shows 'Fred Bloggs' - which is correct End Sub
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd"> <Asset_Collection> <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into: Table: TABLE_A
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
Table: classes Columns: classID, hp Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.Â
create trigger new_hp on curr_chars.char_active instead of insert as declare @hp tinyint select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp go
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.
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)
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
I have the following view (vProcurementPlan)SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID ASReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,dbo.tblRequisition.ROS AS ROS,dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID ASProjectIDFROM dbo.tblProcurementPlan INNER JOINdbo.tblRequisition ONdbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionIDIf I try inserting a record from Access it complains about multiple basetables, I'm happy to write an "instead of" trigger and handle the 5 columnsfrom tblRequisition but as it contains all columns from tblProcurementPlan Idon't want to have to list them separately in any insert or updatestatement.The idea is that a record will be inserted into both tables simultaneouslyupon insert to the view.
I have never written a trigger before and now am seeing the light. Isthere a way to write a trigger so that if a user changes any column ina single row on one table then the trigger will write the value ofthis (these) rows to a second table. I don't want the unchangedcolumns, just the changed columns (with column names...)Thanks,lq
I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires.
I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance.
How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables.
I would like to have the value of a field to be set the return value of System.Web.Security.Membership.GeneratePassword(12,4) every time a a row is inserted. Can you guide with this? Do you have some similar sample code? Thank you very much
I would like to have the value of a field to be set the return value of System.Web.Security.Membership.GeneratePassword(12,4) every time a a row is inserted. Can you guide with this? Do you have some similar sample code? Thank you very much
I have to create a insert trigger that calls a stored procedure usp_a I need to pass two parameters to this stored procedure, value coming from the row inserted... What is the syntax? Any help will be appreciated?
Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.
I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.
I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?
Tax Detail Table Exchange Table
Account# int Country char Description char ExchangeRate Money Amount money Country char
I am working with MSS 6.5. Any and all help will be greatly appreciated.
Really simple question - I have the following trigger for the INSERT event for Tbl_B:
CREATE TRIGGER calc_total ON [dbo].[Tbl_B] FOR INSERT AS
DECLARE @VATRate AS decimal(19,2) SET @VATRate = ( SELECT Val FROM Tbl_Numeric_Refs WHERE Ref = 'VATRate' )
UPDATE Tbl_B SET [Total] = ((@VATRate / 100) * [PriceA]) + [PriceA], [VATRate] = @VATRateHow can I restrict the UPDATE within the trigger to the record(s) being INSERTed? Or am I over-complicating things, and should use a simple UDF for this instead?
Is it possible to pick up the value that is being inserted within the scope of a trigger? For example, if I were to run the following
INSERT INTO people (firstname, lastname) VALUES ('George', 'V')
Would it be possible to access these values in a trigger before they are inserted?
CREATE TRIGGER trigger1 ON people FOR INSERT AS IF EXISTS (SELECT 1 FROM table1 WHERE firstname = <the value being inserted>) BEGIN Print '1' END ELSE BEGIN Print '2' END GO
Rightio - this is probably a simple question for you SQL afficionados.I have written a trigger to update a master table with a CreateDate field. Simple enough I thought but it updates this field when the existing record is edited as well - not so good.This is how it looks like:CREATE TRIGGER CreateDateON MasterFOR UPDATE ASDECLARE@idMaster intSELECT @idMaster = idMaster from Insertedupdate Masterset CreatedDate = getdate()where idMaster = @idMasterGOWell I know I can write an IF statement that will basically say if the field is not null then only update - fair enough - but is there a simpler way to do this? Is there a way I need to write my CREATE TRIGGER statement that ONLY makes it run when it is a NEW INSERT ONLY?THANKS!
