Multiple Update Triggers
Oct 17, 2001What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire
View 1 Replies
ADVERTISEMENT
Multiple Update Triggers Or One Large Trigger With If's
May 1, 2001I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated.
Thanks
Multiple Triggers On A Table Or Encapsulated Triggers
May 12, 2008This 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
Multiple Triggers
Jul 20, 2005When a trigger on one table updates another table, does it activatethe update trigger on the updated table?For ex:1. Table A has a trigger for update2. Table B has a trigger for update that updates table AWill the trigger no table A run when table B trigger updates table A?Thanks
View 2 Replies View Related'Instead Of' Triggers Particularly Update
Mar 10, 2005I am trying to understand how to use instead of trigger using an update.
Here are a few things that I need clarified.
- When using an 'instead of Update' trigger is this replacing any other Update that is happening to the Table.
- Or, is the occurence of the trigger happening after the update?
- Also, I have seen some syntax where people use:
Update table
set field1 = inserted.field1
from deleted, inserted, table
where deleted.field1 = ....
Does this mean that when using the 'instead of' option it keeps track of
what is being deleted and what is being inserted by using such things as deleted.field1 or inserted.field1?
What I need to do is check that the previous value in a particular field is null before updating it. I don't want to update a particular field that already contains a value in it.
Thanks.
Triggers - Run For Multiple Updated
Sep 24, 2005I am new to trigger and I have following question.
I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.
Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?
I hope I make it clear and please help me on this! :o
One Trigger Vs Multiple Triggers ?
Aug 17, 2007
I have a question regarding Triggers.
Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.
Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.
Or I can create three different triggers each for Insrt, update and delete.
Which option is better for performance?
Thanks
Plz Help About Use Triggers To Update Other Table
Apr 30, 2008Plz help
I try to use trigger to see if Component table update at same time update the AssemblySubcomponent
AssemblyID
but it say
Msg 4104, Level 16, State 1, Procedure trigupdateSubcomponentID, Line 6
The multi-part identifier "a.SubcomponentID" could not be bound.
the code look like
create trigger trigupdateAssemblyID on Component
for update, insert
as
begin
if update (ComponentID)
update AssemblySubcomponent set a.AssemblyID= i.ComponentD
from inserted i
join AssemblySubcomponent a on a.AssemblyID =i.ComponentID
end
Creating Multiple Triggers Is Same Sql Script
Jun 14, 2004I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:
CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS
INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS
INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
Triggers Affecting Multiple Tables
May 4, 2004I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".
My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.
Gazzou
Why Triggers Don't Fire On Mass Update
Mar 4, 2005If I have a trigger on a field in a table, and I update one record trigger fire properly. If I do a update to that same field on all records in the table the trigger does not fire. I the error in the trigger, or do I need to change my update statement?
View 2 Replies View RelatedUpdate Foreign Keys Using Triggers
May 18, 2008Hello,
I would like to update foreign keys using triggers. At least thats what I think is the solution when having multiple references to a table in order to avoid the "cycles or multiple cascade paths" error message.
Anyway, here are three tables
Dentist table
dentist_id int identity (auto increment)
Patient table:
patient_id int identity (auto increment)
Appointment:
apointment_id int identity (auto increment)
id_dentist int FK to dentist_id
id_patient int FK to patient_id
I am gooling but cant find a way to make a trigger run only when the dentist_id from dentist table is updated. Also, is there a way to get the new id and old id somehow? I saw some posts with new.dentist_id and old.dentist_id but apparently is not for sql server.
Thank you.
Ntext And Update/insert Triggers
Jul 23, 2005SQL Server 2000 : I have a series of tables which all have the samestructure. When any of these tables are modified I need to syncrhoniseall of those modifications with one other table wich is a sort of mergeof the individual tables with one extra column.For most of these tables this is not a problem. The problem arriveswhen one of the tables has an ntext column which obviously can not beused in an update or insert trigger.Here's an example of one of them:CREATE TABLE tblImages(ID INT IDENTITY(1,1) PRIMARY KEY,Inventory nvarchar(8) NOT NULL,Coll nvarchar(8) NOT NULL,ImageFile nvarchar(128) NOT NULL,ImageNotes ntext NULL,TS timestamp NULLCONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,ImageFile)I then had created an update trigger which looked like this:CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_ImagesFOR UPDATEASBEGINUPDATE tblImages SETInventory = inserted.Inventory,Coll = 'COLLNAME',ImageFile = inserted.ImageFileName,FROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Invventory = tblImages.Invventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileUPDATE tblImagesSET ImageNotes=inserted.NotesFROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Inventory= tblImages.Inventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileEND " & vbCrLf)The first update in my trigger, be it an update or insert trigger,works fine. It crashes with the "Cannot use text, ntext or imagecolumns in the 'inserted' or 'deleted' tables." error in the secondpart.I have read various messages through the Internet on this and severalof them reference using INSTEAD OF triggers and views. I have neverused those before as this is my first work with SQL 2000. None of theexamples of INSTEAD OF triggers I have seen yet use the actual insertedtables and I haven't quite understood how to use them correctly.Can someone help me with the basic syntax as this trigger is one ofseveral that I am going to have to get working.Thank you in advance for any help, assistance, suggestions or"direction pointing" you may provide.
View 1 Replies View RelatedHow To Update Joined Table Using Instead Of Triggers
Jun 25, 2007Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?
thanks in advance.
Triggers Prevent Updates To Multiple Records
Mar 13, 2002I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
Creating Triggers Among Multiple Database Servers
Nov 14, 2000Hello,
I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?
Any help is appreciated!!
Thanks,
Lisa
Real Time Quantity Update Using Triggers
Aug 25, 2001Hi
There are 2 databases db1 and db2 on SQL server 7 with tables 1 and 2 respectively.
Both these tables have a field called Quantity. IF quantity gets updated in table 1 then that change should be immediately reflected in table2.
I though an update on table1 would serve the purpose but this trigger doesn't seem to be working however there are no errors during the syntax check.
The code for the trigger that i have used on table 1 is as below
CREATE TRIGGER [Qty_UPDATE] ON [Table1]
FOR UPDATE
AS
IF UPDATE (Qty)
BEGIN
UPDATE db2.dbo.table2
SET db2.dbo.table2.qty = qty
END
Please help
Thanks in advance
Triggers Running Slow (Update Trigger)
Jul 20, 2005am using FOR UPDATE triggers to audit a table that has 67 fields. Myproblem is that this slows down the system significantly. I havenarrowed down the problem to the size (Lines of code) that need to becompiled after the trigger has been fired. There is about 67 IFUpdate(fieldName) inside the trigger and a not very complex selectstatement inside the if followed by an insert to the audit table. WhenI leave only a few IF-s in the trigger and comment the rest of thecode performance increased dramatically. It seems like it is checkingevery single UPdate() statement. Assuming that this was slowing downdue to doing a select for every update i tried to do to seperateselects in the beginning from Deleted and Inserted and assigningcolumns name to specific variables and instead of doingif Update(fieldName) i didif @DelFieldName <> @InsFieldNamebeginINSERT INTO AUDITSELECT WHAT I NEEDENDThis did not improve performance. If you have any ideas on how to getaround this issue please let me know.Below is an example of what my triggers look like.------------------------------------Trigger 1 -- this was my original designCREATE trigger1 on TableFOR UPDATEASif update(field1)begininsert into AuditSELECT What I needENDif update(field2)begininsert into AuditSELECT What I needEND...... Repeated about 65 more timesif update(field67)insert into AuditSELECT What I needEND---------------------------------------------------------------------------Trigger 2 -- this is what i tried but did not improve performanceCREATE trigger2 on TableFOR UPDATEASDeclare @DelField1 varcharDeclare @DelField2 varchar....Declare @DelField67 varcharSelect@DelField1 = Field1,@DelField2 = Field2,....@DelField67 = Field67From DeletedDeclare @InsField1 varcharDeclare @InsField2 varchar....Declare @InsField67 varcharSelect@insField1 = Field1,@insField2 = Field2,....@InsField67 = Field67From Inserted-- I do not do if Update() but instead compare variablesif @DelField1 <> InsField1beginInsert into AUDITSELECT what I needendif @DelField2 <> InsField2beginInsert into AUDITSELECT what I needend............if @DelField67 <> InsField67beginInsert into AUDITSELECT what I needend----------------------------------------------IF you have any idea how to optimize this please let me know. Anyinput is greatly appreciated. I do not have a problem with triggersdoing what they are supposed to, they are very slow this is myconcern. The reason I gave you two examples is because i suspect ithas something to do with the enormouse amount of code inside thetrigger. both examples perform about the same whether i use the twohuge selects from the Inserted and Deleted or not.Thanks,Gent
View 1 Replies View RelatedUpdate Values From Inserted Or Updated In Triggers
Mar 6, 2008Hi all. I have this problem:
on insert in a new table a have to change one column before insert.
I wrote this trigger:
create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"
how to slove this issue.
Thanks.
Insert / Update Triggers Not Working After Upgrade?
Mar 11, 2014I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE
[M2Data]
GO
/****** Object:Â
Trigger [dbo].[tblContacts_UTrig]Â Â Â Script Date: 3/11/2014 9:07:13 AM ******/
SET
ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?
Duplicate Tables Insert/Update In Another Table? Triggers?
Mar 6, 2002I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
Thanks for help.
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?
SQL Server 2008 :: How To Update Multiple Column With Multiple Condition
Feb 25, 2015I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
Plz Help...update Value In Multiple Db Table Using Single 'update Command'
Mar 18, 2005hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
Is There A Way To Update Multiple Fields Using UPDATE Command
Oct 19, 2005UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies View RelatedMultiple Update With Multiple Condition
Jul 20, 2005I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?
View 4 Replies View RelatedT-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script
Oct 23, 2014I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
Multiple Row Update
Jun 9, 2008Hi everyone, ok so heres my problem. I'm currently writting some scrips to query an simple inventory database. heres what i'm trying to do.
I have a .asp webpage that has a query on it that can dispaly everything in the inventory... old and new in a big html table.
The idea is to not actualy delete anything because i would still like to have a record of it's existance but at the same time i dont want it to clutter my "current" inventory i guess it would call it.
I added another column named deleted which is set to the Yes/No and TRUE/FALSE data types, i also put in a check box at the end of every row on the webpage, the idea here is that if a box is checked it will record that row's id via address bar/Get method and then when i hit submit it will run the update query for those rows checked
i changed my inventory query so that if an item's deleted column is checked to TRUE then it will not be displayed, so showing only the non deleted items.
UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33;
thats what i'm using to change it case by case basis, but what i would like to do is somthing like
UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33,34,67....so on
LicensesID is just a auto incrememted field jsut to give a unique value to a license's row..
does anyone know any tricks to accomplish what i'm trying to do? So far I havn't really found any satisfactory examples online. and i dont really want to completly change the structure of everything unless someone suggust somthing that is clearly a better way of doing things
thanks,
O
Trying To Update Multiple Row
Jan 30, 2007Hi.
I am trying to update multiple row with different value. Is this possible w/ query or should I create a procecure?
Suppose I have two table A and B, I need to update values of A_b = B_b, A_c = B_c where A_a = B_a
below query somewhat shows what I am trying to do...
UPDATE A SET A_b = B_b, A_c = B_c WHERE A_a IN (SELECT B_a FROM B)
Thank you.
Update Multiple At Once In Database
Apr 7, 2008hi all~i very confuse~i using mssqlserver 2005 in visual basic 2005express..but i feel like want to update two table at onces..i faced some problem~i give an example..my tableA got user id, user name and table B got user id and status.In both table, i put user id as primary key and it will auto link each other and i retrieve the user id and user name from table A and user status from table B~..in order to come out the data in gridview with three columns which are user id, user name and user status..in this case, it works perfectly..but when come to update part, i only able to update one table at once, when i trying to update both table, it occurs errors...i did read some forum, some said it is impossible to do it?isn't?then is dere got any other ways to make me update both table at once?my purpose of this is wish to update user id and user name from table A and user status on table B....~i guess maybe my database design concept got problem..hope someone giv help on these... by the way izit foreign key can solve this?and how to put the foregin key?
View 4 Replies View RelatedUpdate Multiple Rows
May 13, 2004Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
Multiple Row Update/Insert
Jun 29, 2005Hi,I have a a table that is primarily a linking table that contains values for categories indid int Indicator ID indtype int Indicator Type can be either 0 or 1catflagnum int Category Flag Number the number of the Category catflagvalue int Cat Flag Value The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time i.e The Data set could look something like indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 1 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 2 'This value will change Catflagvalue = 3 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 3 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 4 'This value will change Catflagvalue = 5 'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?
View 5 Replies View RelatedMultiple Update Statement
Nov 14, 2000If I want to update 2 columns at 1 time with a where clause
like
update table a
set column1 = 8 and
set column2 = 9
where id = 10
I know you can't use the and statement, what is the correct syntax?