MS SQL Server :: Update Trigger Not Firing.

Trigger Not Firing On Update
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ

View Replies !

UPDATE Trigger Firing Too Late?
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.

Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.

USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tr_update_users' AND type = 'TR')
DROP TRIGGER tr_update_users
GO

CREATE TRIGGER tr_update_users
on dbo.Users
FOR UPDATE
AS

DECLARE @m_status_new varchar(100),
@m_status_old varchar(100),
@m_UserID int

SELECT @m_status_new = UserStatus,
@m_UserID = UserID
FROMinserted

SELECT @m_status_old = UserStatus
FROMUsers
WHEREUserID = @m_UserID

IF @m_status_new != @m_status_old
BEGIN
print 'status has changed'
END
ELSE
BEGIN
print 'status has not changed'
END
GO

View Replies !

UPDATE TRIGGER WITHOUT RECURSIVE FIRING
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks

CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE --DT.MODIFIED_BY <> 'DBO' AND
PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end

View Replies !

Trigger Firing And Not Firing
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?

View Replies !

Trigger Not Firing
OK, I'm at a loss..it must be staring me right in the face.

I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.

Any ideas?

CREATE TABLE [dbo].[PIF_MEP99] (
[PIFRecID] [int] NOT NULL ,
[MEPRecID] [int] NOT NULL ,
[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD
CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED
(
[PIFRecID],
[MEPRecID]
) ON [PRIMARY]
GO

INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO

CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99
FOR UPDATE, DELETE
AS
SET NOCOUNT ON

-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary

IF Exists ( SELECT * FROM inserted i
INNER JOIN PIF_MEP99 p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y')
BEGIN
ROLLBACK TRAN
RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP'
END
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

DROP TABLE PIF_MEP99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View Replies !

Trigger Not FIRING With BCP
Hi,

Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....

Davy

View Replies !

Trigger Not Firing ...
Afternoon All ...

I have a trigger on a table that works great when a single record is updated BUT it seems not to fire when I try to do a bulk update as in ...

update TableA

set field3 = ms.field3
FROM TableA as ta,
#ms as ms
where field1 = ms.field1
and field2 = ms.field2

Why does this happen and how can I correct it.

Thanks!

View Replies !

Trigger Not Firing

I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.

I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.

Any help would be appreciated.

View Replies !

Trigger Not Firing, DTS Load
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...

Is DTS capable of updating a SQL Server table without firing the trigger?

I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.

FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26)
Carl

CREATE TRIGGER trig_SAWakeUp ON tsnro
FOR INSERT, UPDATE
AS
DECLARE @snro_stus char(10)
DECLARE @snp_sht_dtm as datetime
SELECT @snro_stus = snro_stus
FROM tsnro
IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED')
BEGIN
SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19))
FROM tsnro

INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END
ELSE
BEGIN
INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END

View Replies !

Insert Trigger Sometimes Not Firing
hi all

i have an issue with an insert trigger sometimes not firing.

here is the trigger

CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS

INSERT into tPABillToAddr
(
chrJobNumber
)

SELECT chrJobNumber
FROM inserted

when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.

thanks

View Replies !

Prevent Trigger From Firing
Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View Replies !

Artificially Firing A TRIGGER
I have a table (table 1) that has a trigger attached to it, which sends data to table 2. Both tables are attached to an appliction.

In theory, the trigger populates table 2 (from table 1) from the data entered from the user interface application.

But when I import data from the backend

via DTS into Table 1, the trigger does not send the data to table 2.

What do I need to do ???

Thank you in advance !!

View Replies !

AFTER INSERT Trigger Not Firing In SQL 2005
Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND

View Replies !

Create Trigger Not Firing Via ODBC
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.

View Replies !

Error W/ Trigger When Firing On 'DELETE XYZ WHERE IN (1,2,3)'
Hi,

Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:

sql Code:

Original
- sql Code

CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync
ON smvcModImageManagerCategory
FOR INSERT, DELETE
AS
DECLARE @pdfId INTEGER;
DECLARE @parentPdfId INTEGER;
DECLARE @grandparentPdfId INTEGER;
DECLARE @parentId INTEGER;
DECLARE @grandparentId INTEGER;
DECLARE @tableName VARCHAR( 255 );

-- If I am being inserted or deleted, and I am not a top level
-- category, then my parent's pdf record needs to be set so that
-- the pdf file is updated
IF (SELECT id FROM Inserted) IS NOT NULL
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Inserted),
@parentId = (SELECT parentCategory FROM Inserted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId);
END
ELSE
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Deleted),
@parentId = (SELECT parentCategory FROM Deleted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId)
END

-- If I am not a top level category, set my parent's pdf to be
-- updated
IF @parentId <> -1
BEGIN
SELECT @parentPdfId = (SELECT pdfManagerId
FROM smvcModImageManagerCategory
WHERE id = @parentId);
UPDATE smvcModPdfManager SET data_last_updated = GETDATE()
WHERE id = @parentPdfId;
END
GO

CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO

However, when I execute a statement like:

sql Code:

Original
- sql Code

DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))

DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))

I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.

Thank you.

View Replies !

Trigger Not Firing On Cascade Delete Table
I have three tables:
BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records
BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table
Member

This issue:
> When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should
> If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire

Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this?

Any suggestions or comments would be great.

Thanks,

Mike

View Replies !

Trigger Firing Multiple Stored Procedure
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.

CREATE TRIGGER tr_new_file ON FTPLog
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @filename varchar(50), @logtime datetime
DECLARE c1 CURSOR
FOR SELECT filename, logtime
FROM inserted
OPEN c1
FETCH NEXT FROM c1
INTO @filename, @logtime
WHILE @@fetch_status = 0
BEGIN
EXEC sp1 @filename, @logtime
FETCH NEXT FROM c_inserted
INTO @filename, @logtime
END
CLOSE c1
DEALLOCATE c1
END

PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.

PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.

SOLUTIONS and SUGGESTIONS highly appreciated.

View Replies !

Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP

View Replies !

Trouble With Update Trigger Modifying Table Which Fired Trigger
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 Replies !

Trigger UPDATEing Another Table With An UPDATE Trigger...
Hi,
Let's say I have Table A & B. I have a trigger for UPDATE and INSERT on table A that updates a field in table B. This works great. I have just realized that I need to keep the field in table B from being updated by anything other than table A's trigger. Still with me... So I added a trigger to table B for INSERT and UPDATE that looks like the following:

IF UPDATE(Quantity)
BEGIN
RAISERROR(50001, 16, -1)
ROLLBACK TRAN
END

The user can change anything in table B except the Quantity field. Is there a way to disable the trigger on table B from firing when the UPDATE is fired from the trigger on table A?

-Alan

View Replies !

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:

Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View Replies !

Trigger To Update One Record On Update Of All The Tables Of Database
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 Replies !

Update Trigger Behaviour W/o A Trigger.
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 Replies !

How To Use Update Column For 2 Columns In Update Trigger
I wanted to write a trigger in SQL 2000 checking for an update for 2 columns. If I do one column like "if update(PATIENT_ACPT_STATUS)"
it works fine. If I want to add a second column I tried to
use "if update(PATIENT_ACPT_STATUS,pat_note)" it doesn't like the syntax. BOL says you can use more than one column but does not give an example. Has anyone tried this yet? Thanks

View Replies !

Trigger To Update A Table On Insert Or Update

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 Replies !

Update Trigger - Update Query
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 Replies !

Update Trigger To Update Another Table
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 Replies !

INSTEAD OF UPDATE Trigger To Hold Update

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 Replies !

UPDATE Trigger Issue When Using UPDATE
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 Replies !

Help Using Update Trigger
Hi
How do i use update trigger...
What I want is that u got "money" and there is a thing you want to buy
You got total 200 money then this thing cost 300 money, and this dont work becouse
200-300 =-100
Now how can i do like it register that its under zero, i know u have to use update trigger...
Please help me,
Thanks.
Even knutli

View Replies !

Update Trigger
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 Replies !

UPDATE Trigger For PK
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 Replies !

Update Trigger
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 Replies !

Help With Update Trigger
Hi all,I know squat about triggers so was hoping somebody could point me in theright direction. I wanted to copy an email address field from a salesmantable to a note field in a customer table. Seems easy enough for a one timeupdate. But I would like to add a trigger to auto-update the customer tableanytime an email address changes in the saleman table or a new salesmanrecord is added.Here's my update script (this copies the salesman email address to each ofhis customers)UPDATE CUSTOMERSSET NOTE_5 = SALESMAN.EMAIL_ADDRFROM CUSTOMERS INNER JOINSALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NOHow can I turn this into a trigger for automatic updates?Thanks for any help.

View Replies !

Update Trigger Or ?
Hello,I've a problem where some data gets updated but I don't know whichprocess (SP) is responsible for it (it's an old installation which I'vetaken over).Is it somehow possible to know which process/user/SP performs an updateon a special table/column?I was thinking about to implement an update trigger but unfortunatelyI've no idea how to figure out the "parent process" which wasresponsible for the update.I'm using MS SQL Server 2000.Thanks Joerg

View Replies !

TRIGGER After UPDATE
Hi there!I need to write a trigger that will check referential integrity of mydata. I have few FOREIGN KEY constraints but, as You probably konow, thecannot be deferred (in the meaning of SQL 92 standard). So I decided toadd NOCHECK CONSTRAINT ALL to the modified table and then run a trigger(after secon altertion of my table). But I cannot write a trigger forALTER. I found something on msdn, byt their example doesn't work. Toshow what's my problem look at this example:CREATE TABLE Indeksy(id_indeksu INT CONSTRAINT indeksy_pkey PRIMARY KEY,numer INT CONSTRAINT wymagany NOT NULLCONSTRAINT unikatowy UNIQUE);CREATE TABLE Studenci(id_studenta INT CONSTRAINT studenci_pkey PRIMARY KEY,indeks INT CONSTRAINT indeks_studenta REFERENCES Indeksy(id_indeksu)ON DELETE CASCADEON UPDATE CASCADE--DEFERRABLE INITIALLY DEFERREDCONSTRAINT tylko_raz UNIQUE,nazwisko VARCHAR(255) CONSTRAINT nazwisko_wymagane NOT NULL);GOCREATE TRIGGER ReferentialIntegrityTriggerForStudenciON DATABASEAFTER ALTERASBEGINDELETE FROM Studenci WHERE Studenci.Indeks NOT IN (SELECT id_indeksuFROM Indeksy);ENDGOENABLE TRIGGER ReferentialIntegrityTriggerForStudenci ON Studenci;GOINSERT INTO Indeksy VALUES (1,1111111);INSERT INTO Indeksy VALUES (2,1211111);INSERT INTO Studenci VALUES (1,1, 'Kowalski');INSERT INTO Studenci VALUES (2,2, 'Nowak');--deferredALTER TABLE StudenciNOCHECK CONSTRAINT ALLINSERT INTO Studenci VALUES (3,5, 'Odraczacz');INSERT INTO Studenci VALUES (4,130, 'Powolny');INSERT INTO Studenci VALUES (5,4, 'Grabowski');INSERT INTO Indeksy VALUES (3,1121111);INSERT INTO Indeksy VALUES (4,1112111);INSERT INTO Indeksy VALUES (5,1111211);ALTER TABLE StudenciCHECK CONSTRAINT ALLWhen I run this script I get a message: Msg 156, Level 15, State 1,Procedure ReferentialIntegrityTriggerForStudenci, Line 4Incorrect syntax near the keyword 'ALTER'.Without it INSERT INTO Studenci VALUES (4,130, 'Powolny'); insertsinvalid data that cannot be checked... Of course this is only an example.Could you, please, write simillar, WORKING :-) trigger for me?Thanx!Wojciech

View Replies !

Trigger And Row Update Help
I have a table that matches up Securities and Exchanges. Individualsecurities can belong on multiple exchanges. One of the columns, namedPrimaryExchangeFlag, indicates if a particular exchange is the primaryexchange for that symbol. Each symbol can only have one primaryexchange.I am trying to write a insert/update/delete trigger that enforces thisrule. The rules I have thought of are as follows:Insert If new row has flag set, turn off flag for other rows forthat symbol. Otherwise, do nothing.Update If updated row has flag set, turn off flag for other rowsfor that symbol. Otherwise, set flag on first (MAX or MIN or TOP 1???) row for that symbol.Delete If deleted row had flag set, set flag on first row for thatsymbol. Otherwise, do nothing.My basic problem is how to do this when the trigger gets thrown formultiple rows. (Since SQL does not throw individual triggers for eachrow.)Thanks.

View Replies !

Update Trigger Plz Help
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 Replies !

Update Trigger - Old Value
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 Replies !

Update Trigger
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 Replies !

Update Trigger
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 Replies !

Trigger On Update
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 Replies !

Update Trigger
This is something I have limited experience on. I need to create an update trigger after insert on one table that updates a completely different production table. My development server is being re-done and I'm not sure when it will be back on line. I have created some audit tables using triggers but they a simple inserts into a new table.

I believe the trigger statement should look like this, any advice would be appreciated.

Thanks in advance.

CREATE TRIGGER OB$1InsertAudit ON OB$0001
AFTER INSERT
AS
Begin
Update AX$0001
Set AX$0001.receiveddate=getdate(), AX$0001.docreviewstatus=null where Ob$0001.objid=AX$0001.refobjid and OB$0001.recordtype=6 and
OB$0001.descriptor in('Ancillary Services Tracking Form',
'FCE Referral Request',
'Remain At Work',
'Voc Rehab Job Retention')

End

View Replies !

Update Trigger
Hi,

If I have a situation where a particular SQL statement updates three records on a table and there is an Update trigger defined on the table, then how many times will the trigger fire - three times or one time.

Thanks in advance.
Raj

View Replies !

Update Trigger
I have this table:

CREATE TABLE [dbo].[EB_Eprom] (
[EpromID] [int] IDENTITY (1, 1) NOT NULL ,
[Naam_Spel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Versie] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checksum1] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checksum2] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Omschrijving] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_vrijgave] [smalldatetime] NULL ,
[Kabinet] [int] NULL ,
[Merk] [int] NULL ,
[Wet] [int] NULL ,
[Bestand_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bestand_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lastedit] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and i created this trigger:

CREATE trigger CT_EB_EpromBestanden on dbo.EB_Eprom
after update
as
update EB_Eprom
set eb_eprom.bestand_1 = '../data/'+str(b.bestand_1) ,
eb_eprom.bestand_2 = '../data/'+str(b.bestand_2)
from EB_Eprom a join inserted b
on a.EpromID= b.EpromID

the thing that this trigger must do is update the given value with the custom path ../data/

Can someone tell me what i do wrong!!
Thanx already
Cheerz Wimmo

View Replies !

Update Trigger
hi folks.

Í want to do this:
/***************************************/
CREATE TRIGGER TRGfechaModificacion
ON UsuariosVencimientos
AFTER UPDATE AS
DECLARE @updatedID AS int
BEGIN
--Get the last ID updated
SELECT @updatedID = @@updated --???
-- Updated this record with the actual datetime
UPDATE
UsuariosVencimientos
SET
Fecha_Actualizacion = getDate()
WHERE
id = @updatedID
END
/***************************************/

Resume:

I want to get the 'id' updated so I can used for the update query ;)

thanx

View Replies !

Update Trigger

View Replies !

TRIGGER FOR UPDATE
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 Replies !