Trigger And Update() Function!
Jun 28, 1999
In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if
used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.
Below there is a piece of code that demostrate the false documentation
assertion in the case of a trigger fired by an INSERT operation, infact even
if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.
Now the SP1 don't solve this BUG, even if i had read some month ago (before
that SP1 was out) that in SP1 this will be fixed!
Anyone Can Help me to solve this problem, indicating me where i can post the
problem to sensitize the Microsoft's Service Pack Factory ?
Thank in Advance
PS:Sorry for my Bad English .... correct me please if some part are not
readable!
------[Cut Here]-------------------
CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER )
GO
CREATE TRIGGER TriggerIU_Test
ON TEST
FOR INSERT, UPDATE
AS
IF UPDATE(FIELD1) PRINT 'Field 1 Touched'
IF UPDATE(FIELD2) PRINT 'Field 2 Touched'
GO
print 'InsertIng '
INSERT INTO TEST (FIELD1) VALUES (2)
print 'Updating '
UPDATE TEST set FIELD1 = 1
GO
DROP TABLE TEST
------[Cut Here]-------------------
View 2 Replies
ADVERTISEMENT
Mar 8, 1999
In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.
Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?
View 4 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
May 13, 2008
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
View 23 Replies
View Related
May 30, 2008
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
View 3 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Dec 17, 2001
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end
View 1 Replies
View Related
May 30, 2008
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)
View 7 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Apr 3, 2008
hi!
i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.
i want to avoid the trigger launching for the last column (UpdatedOn).
how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.
another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)
thank you in advance.
View 8 Replies
View Related
Nov 27, 2007
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.
I need help on how can I capture insert trigger event VS 2003 console application?
View 2 Replies
View Related
Jul 20, 2005
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.
View 2 Replies
View Related
Dec 8, 2006
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
table 1 = stock table
table 2 = Purchase order table
in table 2 there are line items that have ordered quantities for parts that we have ordered
in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1
quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this
table 2
poNumber partNumber quantity
1 516 1
2 516 12
3 516 4
table 1
partNumber inStock onOrder
516 0 17(this is what i am trying to figure out how to compute)
any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.
View 3 Replies
View Related
Mar 7, 2008
HI ALL,
I AM USING SQL SERVER 2005.
I HAVE RETURN A RECURSIVE FUNCTION TO FIND OUT WHETHER THE NEXT DATE DOES NOT FALL WITHIN HOLIDAYS
BUT I AM GETING THIS ERROR
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
THE CODE I USED IS
alter FUNCTION [dbo].[GetNextDay](@dt datetime , @empcode varchar(50) )
RETURNS datetime
AS
BEGIN
DECLARE @zoneid VARCHAR(50)
declare @lvlflag varchar(50)
declare @utdt DATETIME
DECLARE @RETDT DATETIME
DECLARE @COMPDT DATETIME
Select @lvlflag= b.ulm_user_field_flag from bbraun_emis.dbo.emp_reference a join bbraun_emis.dbo.user_lvl_master b on b.ulm_user_lvl_id = a.ER_USER_LVL and a.er_emp_code = @empcode
SELECT @zoneid = ZONECODE FROM bbraun_emis.dbo.VWREGIONLINK WHERE CITYCODE IN (SELECT DISTINCT HM_CITY_CODE FROM bbraun_emis.dbo.HOSP_MASTER WHERE HM_HOSP_CODE IN (SELECT HER_HOSP_CODE FROM bbraun_emis.dbo.HOSP_EMP_REL WHERE HER_EMP_CODE in(@EMPCODE)))
select @compdt = holiday_date from oriffconnect.dbo.holiday_master where zone_code = @zoneid and field_staff = @lvlflag and holiday_date = @dt
if(@@ROWCOUNT = 0)
begin
Select @utdt = DATEADD(dd,1,@utdt)
SeT @utdt = ([dbo].[GetNextDay](@utdt , @empcode))
end
IF(@@ROWCOUNT <> 0)
begin
set @utdt = @dt
end
Select @RETDT = @utdt
RETURN @RETDT
END
PLEASE HELP
View 2 Replies
View Related
Jan 17, 2012
Multiple rows to insert:
---------------------
insert into Customer(CustomerId,Name,Value)
select CustomerId,Name,Value
from CustomerTemp
Trigger in Customer table that invoke a function:
alter TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS
update Customer
set Percentage = dbo.GetPercentage((select Value from inserted))
where CustomerId = (select CustomerId from inserted)
I'm getting the error for the multiple row.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Is there a way to let me insert multiple rows, using the trigger that invoke a function ?
View 1 Replies
View Related
Aug 22, 2006
Hi All,
I wanted to query the xml returned by the eventdata() function in a ddl
trigger to view it in result set.
I made that code but it returned null, any help please?
create trigger DatabaseEvents
on database
for
ddl_database_level_events
as
--select
eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @data xml
select @data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post
Time'
from
@data.nodes('/EVENT_INSTANCE')
as EventsTable(Col)
go
Thank you in advance,
Bishoy
View 1 Replies
View Related
Apr 6, 2004
Hello All,
I've been adviced without much detail that the best way to handle updates is with an Update Trigger to write to an AuditTable.
I always thought it was better to avoid triggers.
I'm getting confused very fast in reading about triggers.
Could someone please point me in the right direction?
Thank you,
Tina
View 6 Replies
View Related
Dec 2, 2004
i am using address table to store the address of employees, agents, students; so can't use cascade update and delete. so i decided to use trigger but i got stuck here as well.
Here employeeid is the PK of employee id. so when it is updated, i want to update that id in corresponding address table as well.
so what i need is to find out the previous value of EmployeeID before updating to use in WHERE clause and new EmployeeID to use in SET clause.
<code>
CREATE TRIGGER [AddressUpdate] ON [dbo].[MCS_Employee]
FOR UPDATE
AS
IF UPDATE(EmployeeID)
BEGIN
UPDATE MCS_Address SET EmployeeID = 'i am stuck here; how to know the previous value?
WHERE EmployeeID = ???
END
</end>
ironically, PK in my table can be updateable as well.
View 2 Replies
View Related
Sep 1, 2005
Hello all. I have a table which is the exact replication of a different table. Now if an update takes place in the original table I want to copy that row into the new table without the update.I have this but I am not sure if it works.Create Trigger Content_Archive on content
For Update
As
Insert Into content_audit Select * From DeletedI dont know if Updated exists so I am using Deleted. It is returning both rows (before and after update). And the insert should take place as soon as an update takes place in any field in the content table.
View 3 Replies
View Related
Feb 21, 2001
Hello, I have two tables, looks as follow.
TOPIC
------
TOPIC_ID
STATUS_ID
TOPIC
THREAD
-------
THREAD_ID
TOPIC_ID
STATUS_ID
THREAD
THREAD_DATE
NAME
I want to update STATUS_ID in TOPIC when I post a new THREAD.
I guess the best would be to use a trigger?
Don't know much how you write them so if someone please could help or point me in right direction.
My plan is to always show the updated STATUS_ID in TOPIC while I have history in the THREAD TBL.
I am using MS SQL 7.0
Thanks for a great site.
Linkan
View 2 Replies
View Related
Feb 22, 2001
Hello,
I am new to this and on my way to learning. I hope someone can help me with a trigger. I want to update status of one table when I insert a new threat in another. This is what I have come up with so far.
CREATE TRIGGER [trg_Update_Status] ON tForumThread
FOR INSERT
AS
BEGIN
UPDATE tJournalTopic (STATUS_ID)
SELECT STATUS_ID
FROM Inserted
Thanks,
Linkan
View 2 Replies
View Related
Dec 17, 2002
I need to be able to select the row or specific field that was updated in an update trigger. I dont have any time-stamp or before-after value columns to compare. Please help!
View 4 Replies
View Related
Aug 20, 2001
I HAVE TWO TABLES IN THE DATABSE T1 AND THE SECOND TABLE(T2) IS FOR AUDITING.
THE STRUCTURE OF BOTH THE TABLES IS SIMILAR WITH ONE MORE COLUMN 'STATUS' IN THE T2.
I HAVE ALREADY WRITTEN THE TRIGGERS FOR THE INSERT AND DELETE TRIGGERS SO THAT I CAN SAVE THE
RECORD WITH STATUS OF 'NEW INSERT' OR 'DELETE' OR 'UPDATE' IN THE AUDIT TABLE(T2).
HELP ME IN WRITING THE TRIGGER FOR UPDATE.
MY PROBLEM IS I DON'T KNOW HOW TO STORE THE UPDATED COLUMNS VALUES INTO VARIABLES AND SO THAT
I CAN STORE THE OLD VALUES INTO THE AUDIT TABLE(T2).
I M USING SQL SERVER 6.5, SO WE CAN NOT USE COLUMNS_UPDATED() IN THE TRIGGER.
PLEASE SUGGEST ME THE CODE SO THAT I CAN STORE THE OLD ROW IN THE TABLE(T1) TO TABLE (T2)
WITH STATUS 'UPDATE' IN THE T2.
PLEASE SUGGEST ME..ITS URGENT.
THANKS IN ADVANCE
HARISH
=============================
/*test trigger for insert status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_insert') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_insert
GO
CREATE TRIGGER tri_t1_insert ON dbo.t1
FOR INSERT
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from inserted
insert into t2(stamp,name,status) values(@v1,@v2,'NEW INSERT')
end
GO
========================================
/*test trigger for delete status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_delete') and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_delete
GO
CREATE TRIGGER tri_t1_delete ON dbo.t1
FOR delete
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from deleted
insert into t2(stamp,name,status) values(@v1,@v2,'DELETE')
end
View 1 Replies
View Related
May 29, 2002
I have an update trigger on a table on my transactional database that inserts a row of data into another database (audit database)for any modification made on the transactional database.
So if i modify a row on tran db it will write the data modified as a new row in the audit db.
This works fine if I am updating only 1 row with each Update statement. However if I update more than 1 row (multiple rows) with the same Update statement, the update trigger only inserts the last row modified in the audit database. So I lose record of any other rows modified with the same update statement.
Is there a way by which i can change my Update trigger or something, so I get all the rows updated by 1 update statement inmy audit database???
Thanks a bunch,
Judy
View 4 Replies
View Related
Apr 9, 2001
Hello All,
How do I create an update trigger that only updates the record that is being updated?
TIA,
Terry
View 1 Replies
View Related
Mar 4, 2005
well basically i have a table with 3 columns in a table called TEST like:
TEST1 TEST2 TEST3
------- ------- -------
NULL NULL NULL
these columns can allow nulls. What i whant to do with my trigger is do a after trigger and check after the load if a certain column is NULL place a X instead like a flag but only on the columns that are NULL how would i do this.
plz help
View 2 Replies
View Related
Oct 14, 2005
Is there a way I can get the old value of a specific field when using an update trigger?
e.g. I want to use the condition 'If Update(Column) '
in odrer to create a logfile which stores the old and new value of a field.
The new value can be get from a Select from Inserted table.
Is there a way I can get the old value (before update) also??
Regards,
Manolis
View 1 Replies
View Related
Mar 7, 2006
I have created a table with the following columns Jobnumber varchar(20), weight real(4), freightcost money(8), trackingnumber vchar(50), comments varchar(2000) and voidid varchar(3)
I wrote a trigger that updates this data based on the voidid to update the package table as followed:
CREATE TIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
DECLARE @JOBNUMBER CHAR(20)
DECLARE @WEIGHT REAL(4)
DECLARE @FREIGHTCOST MONEY(8)
DECLARE @TRACKINGNUMBER CHAR(25)
DECLARE @SHIPMETHOD CHAR(50)
DECLARE @voidid char(2)
SELECT @JOBNUMBER=JOBNUMBER,
@WEIGHT=WEIGHT,
@FREIGHTCOST=FREIGHTCOST,
@TRACKINGNUMBER=TRACKINGNUMBER,
@SHIPMETHOD=SHIPMETHOD,
@VOIDID=VOIDID
FROM INSERTED
UPDATE PACKAGE
SET PACKAGE.WEIGHT = @WEIGHT,
PACKAGE.FREIGHTCOST = @FREIGHTCOST,
PACKAGE.TRACKINGNUMBER = @TRACKINGNUMBER,
PACKAGE.COMMENTS = @SHIPMETHOD
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'N'
UPDATE PACKAGE
SET PACKAGE.WEIGHT = '',
PACKAGE.TRACKINGNUMBER = '',
PACKAGE.COMMENTS = 'UPS VOID',
PACKAGE.FREIGHTCOST = ''
WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
AND @VOIDID = 'Y'
I am getting the following error see attached.
Any help would be great Thank you!
View 2 Replies
View Related
May 10, 2006
Hallo there,
I'm totally new to writing triggers, but managed to get a trigger to update a specific column (ufINCTcost) on change of another column (ufINCKm)
My problem is that the trigger performs this update on ALL rows, which makes it very slow. How can I get it to only update the column on the row where the change was made ?
My trigger looks as follows:
CREATE TRIGGER updateincidents ON [dbo].[_rtblIncidents]
FOR UPDATE, INSERT
AS
IF UPDATE (ufINCKm)
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCTcost=dbo._rtblIncidents.ufINCKm+dbo._rtblInc idents.ufINCToll+dbo._rtblIncidents.ufINCParking+d bo._rtblIncidents.ufINCFlight+dbo._rtblIncidents.u fINCRental
Hope you can help !!
View 5 Replies
View Related
Nov 11, 2006
Hi,
I've got 2 tables, EMPLOYEE and STORE
The EMPLOYEE table holds all the basic stuff you would want to know about an employee and it contains a STORE_CODE attribute that points to the same attribute in the STORE table (to keep track of the store they work at). The STORE table has the STORE_CODE attribute, a NUM_EMP attribute that keeps track of the number of employees at that store, and some other information that is of no relevance to the question.
I've written the following TRIGGER to update the NUM_EMP attribute in STORE everytime a row is inserted or deleted from EMPLOYEE. It works fine for inserts and deletes but I am clueless as how to make it work for updates (an EMPLOYEE transfers to another store). I appreciate any feedback and please feel free to tell where I've gone wrong so far.
Thanks!
CREATE TRIGGER [UPDATENUMEMP] ON [EMPLOYEE]
FOR INSERT,UPDATE,DELETE
AS
DECLARE @STORECODE INT
DECLARE @NUMEMP INT
/*DELETE CASE*/
IF (NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE DELETECURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMDELETED
GROUP BY STORE_CODE
OPEN DELETECURSOR
FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP - @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
END
CLOSE DELETECURSOR
DEALLOCATE DELETECURSOR
END
/*INSERT CASE*/
IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE INSERTCURSOR CURSOR FOR
SELECTSTORE_CODE, COUNT(*) AS NUMEMP
FROMINSERTED
GROUP BY STORE_CODE
OPEN INSERTCURSOR
FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATESTORE
SETNUM_EMP = NUM_EMP + @NUMEMP
WHERESTORE_CODE = @STORECODE
FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
END
CLOSE INSERTCURSOR
DEALLOCATE INSERTCURSOR
END
GO
View 5 Replies
View Related