'Instead Of' Triggers Particularly Update
Mar 10, 2005
I 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.
View 1 Replies
ADVERTISEMENT
Oct 17, 2001
What 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
View Related
Apr 30, 2008
Plz 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
View 4 Replies
View Related
Mar 4, 2005
If 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 Related
May 18, 2008
Hello,
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.
View 2 Replies
View Related
Jul 23, 2005
SQL 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 Related
Jun 25, 2007
Hi
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.
View 4 Replies
View Related
Aug 25, 2001
Hi
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
View 1 Replies
View Related
May 1, 2001
I 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
View 1 Replies
View Related
Jul 20, 2005
am 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 Related
Mar 6, 2008
Hi 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.
View 5 Replies
View Related
Mar 11, 2014
I 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?
View 8 Replies
View Related
Mar 6, 2002
I 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.
View 2 Replies
View Related
Oct 23, 2014
I'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.
View 1 Replies
View Related
May 12, 2008
This 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
View 12 Replies
View Related
Jan 3, 2007
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
View 1 Replies
View Related
May 31, 2007
Hello All,
I have to write Trigger for Update,
I have two tables, one is for storing records of current values, and one is for storing history of values.
How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values.
Help me.
General Problem
View 1 Replies
View Related
Jun 15, 2007
I need to create a set of rows every time a new row is inserted into a table.
Example (I think this would work)...
select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id)
Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values?
Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger?
Sorry if I sound confused. I am.
View 2 Replies
View Related
Dec 6, 2007
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value.
SET @ActionTaken = 'A'
IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
View 3 Replies
View Related
Dec 21, 2007
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
View 3 Replies
View Related
May 9, 2004
Hi All,
I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
Thank you,
Mike
View 3 Replies
View Related
Jun 17, 2004
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set
(Lastname = @Lastname)
where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
View 10 Replies
View Related
Mar 5, 2006
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
View 3 Replies
View Related
Dec 4, 2001
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS
IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null
else
update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null
View 4 Replies
View Related
Dec 5, 2001
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS
UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
View 1 Replies
View Related
Jan 11, 2002
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
View 1 Replies
View Related
Jan 31, 2002
Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:
create trigger mytrigger
on sales, users
as...
of course, this don´t work :)
View 1 Replies
View Related
Aug 3, 2001
Hey everyone...
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
Thank you...
View 3 Replies
View Related
Oct 24, 2001
I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As
Declare @U_Name varchar(65)
Declare @O_Name varchar(65)
Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey
Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name
Commit Tran
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
Chris
View 1 Replies
View Related
Jul 20, 2000
Hi,
I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
View 1 Replies
View Related
Nov 1, 1999
I have an insert trigger on table A which inserts records into table B.
This insertion into B fails( or may fail ) at certain conditions.
I do not want the insertion into A to be affected because of this. Any ways to achieve this ?
View 1 Replies
View Related
Oct 8, 1999
Hi,
I have 3 tables A,B C and Triggers on each table like.
for insert/update on table 'C' calculates sum(xxfield) and updates
table 'B'.
for insert/update on table 'B' calculates sum(yyfield) and updates
table 'A'.
This works fine if I insert rows OneByOne.
Problem :- Doesn't work if I post entries(hundreds) in to table 'C' using a Query.
whats happening. Can anyone help Me????????
View 1 Replies
View Related
Nov 12, 2001
How do I know if the triggers on table has been disabled or not. Thanks for your ideas!!!!
View 3 Replies
View Related
