Hi: I have created the following trigger. alter trigger deletecategories on Product_Tbl_ProductDetailsfor delete as delete CategoryID from Product_Tbl_Catagory,Product_Tbl_ProductDetailswhere Product_Tbl_Catagory.CategoryID=Product_Tbl_ProductDetails.CategoryIDdelete SubcategoryID from Product_Tbl_SubCatagory,Product_Tbl_ProductDetailswhere Product_Tbl_SubCatagory.SubcategoryID=Product_Tbl_ProductDetails.SubcategoryID While deleting the record "delete from Product_Tbl_ProductDetails where CategoryID=1"I have encountered the below error Error:Msg 208, Level 16, State 1, Procedure deletecategories, Line 4Invalid object name 'CategoryID'. Can any one send me the correct query Thanks:Suresh
When I execute a query that updates multiple rows, the trigger issues an error. Does anyone see the problem ?
Listed below is the Error, Trigger, and Query.
Server: Msg 512, Level 16, State 1, Procedure TR_KYLAB_LOT_GRADE_01, Line 5 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.
CREATE TRIGGER TR_KYLAB_LOT_GRADE_01 ON KYLAB_LOT_GRADE FOR UPDATE NOT FOR REPLICATION AS Update KYLAB_LOT_GRADE SET updateUserName = user_name(), updateDateTime = getdate() WHERE KYLAB_LOT_GRADEID = ( SELECT INSERTED.KYLAB_LOT_GRADEID FROM INSERTED)
UPDATE KYLAB_LOT_GRADE SET OLD_ITEM = 'TEST' WHERE ITEM_TYPE_ID = 'FG' AND ITEM_NO = '2011312LV0' AND LOT_NO IN ('1000', '1001')
Hi everybody, My SQL 2000 refuses to recognize the INSTEAD OF trigger. I just pasted a query from the SQL Server Books Online article "Designing INSTEAD OF Triggers" into the Query Analyzer and was greeted with:
Server: Msg 170, Level 15, State 1, Procedure IO_Trig_INS_Employee, Line 2 Line 2: Incorrect syntax near 'INSTEAD'.
The following trigger is in a table called "PERSON". When I attempt to save it (not even testing it yet), I get the message "Incorrect syntaxt near 'GO'". Please advise as to what the error might be.
CREATE TRIGGER InsertHeadCount ON dbo.person AFTER INSERT AS INSERT INTO HEADCOUNT(person_id,activity_id,no_answer,not_coming,coming) SELECT i.person_id,a.activity_id,1,0,0 FROM INSERTED i CROSS JOIN ACTIVITY a GO
Background: I'm building an application that includes tables PERSON, ACTIVITY, and HEADCOUNT that look like this:
PERSON person_id (int) person_name (varchar(50)) ...etc
The intended functionality of the trigger is as follows: Suppose that:
a) The ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3. b) The HEADCOUNT table is already populated with 30 records.
TRIGGER OBJECTIVE: When a new record is added to the PERSON, say with person_id= 10, insert one record in the HEADCOUNT table FOR EACH activity in the ACTIVITY table for that person. Thus, if person #10 is added to the PERSON table, then the trigger would add the following records to the HEADCOUNT table:
I made a trigger for delete just like this."CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]FOR DELETEASDeclare @severity int,@IdNmbr nvarchar(10)Set @Severity = 0Declare NoId Cursor Local Static forSelect [Promo_Id] from deletedOpen NoIdWhile 1=1BeginFetch NoId Into@IdNmbrIf @@Fetch_Status <> 0BreakIf Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@IdNmbr)Set @Severity = @Severity+1ElseBeginDelete From MbPromo_hdr where [Promo_Id]=@IdNmbrEndEndClose NoIdDeallocate NoIdIf @Severity = 0CommitElseBeginRollBackPrint 'Data Cannot Be delete'EndGo"When i delete the record from Enterprise manager it give me an error"Another user has modified the content of this table or view. The databaserow you are modifying no longer exists in the database."Why? And it happen with all of the record at my table--Message posted via http://www.sqlmonster.com
I have a table:----------------------------------------------------CREATE TABLE CATEGORY (CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))GO----------------------------------------------------and i try to create this trigger, but i fail:----------------------------------------------------CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]FOR INSERTASDECLARE @TEMP_ID AS INT, @COUNTER AS INTUPDATE AI_CATEGORYSET @TEMP_ID = CATEGORY_ID, @COUNTER = 1IF PARENT_CATEGORY_ID IS NOT NULLBEGINWHILE@TEMP_ID IS NOT NULLBEGINUPDATE AI_CATEGORYSET @TEMP_ID = PARENT_CATEGORY_ID,@COUNTER = @COUNTER + 1ENDENDUPDATE CATEGORYSET DEPTH = @COUNTER----------------------------------------------------And i get the following Error using SQL Server 2000:Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7Invalid column name 'PARENT_CATEGORY_ID'.Any clue?Coosa
I have the following CREATE TRIGGER dbo.tgrCacheCustomers ON dbo.Customers FOR INSERT, UPDATE, DELETE AS EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers' and I get the following error that I dont understand:
Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.
The trigger does set my CommAudit field to 1 when imaSlsCls is changed, but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below. Thanks for any help, Tom
*** TRIGGER *** CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster FOR UPDATE AS IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted) BEGIN update tblItemMaster set commaudit = 1 from inserted, tblItemMaster where inserted.imaitnbr = tblItemMaster.imaitnbr END
*** UPDATE COMMAND *** UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1
*** ERROR *** Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5 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.
I have SQL Server 2000, and our web application is in WebObjects.
I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.
This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.
Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.
Here is the relevant portion of the trigger:
create trigger t_press_run_change on dbo.press_run_line_item for insert, update, delete as
updatepress_run_line_item setis_changed = 1 from deleted d join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id where(p.is_changed = 0 or p.is_changed is null) AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0)) OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0)) OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0)) OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0)) OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,'')) OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,'')) OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,'')) OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,'')) OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,'')) OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,'')) OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,'')) OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0)) OR(isNull(p.address_company,'')<>isNull(d.address_company,'')) OR(isNull(p.address1,'')<>IsNull(d.address1,'')) OR(isNull(p.address2,'')<>IsNull(d.address2,'')) OR(isNull(p.address_city,'')<>IsNull(d.address_city,'')) OR(isNull(p.address_state,'')<>IsNull(d.address_state,'')) OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,'')) OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,'')) OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))
I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:
empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.
Problem: When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said: Command attempted:
Similar errors occur periodically, and the filename (C: empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.
Please can anyone help me code the trigger below. I have had errors pointing to "sp_configure 'SQL Mail XPs', 1;" and "RECONFIGURE", that this lines should be not where I have placed them. I have tried other combinations and have not worked at all! Is any other ways I can achieve this all?
Code Snippet
CREATE TRIGGER reminder ON Bug_Report FOR INSERT, UPDATE, DELETE AS sp_configure 'SQL Mail XPs', 1; EXEC master..xp_sendmail 'MaryM', 'Don't forget to print a bug report.' RECONFIGURE
hi....... i create a trigger after delete which work perfactly fine on one sqlserver2005 machine but not at another.......machine having sqlserver2005 this trigger effect 15 tables from which i am deleting data...when trigger fires..... now it cause following error when i fire ir on another machine error source: .net sql client data provider error message:maximun stored procedure,triggers,functions,or view nesting level exceeded (limit 32) plzzzz give da solution.....its really frustrating me.... thanks in advance
The many that I have spoken to all are clueless on this one. Thanks in advance for the right solution!
The insert trigger I created works fine (well, nearly fine), except that AFTER the first insert operation (ie second, third etc), it always produces the correct results BUT FOR THE PREVIOUS INSERTED ROW. It is as if there is a latency of one row in the temp table INSERTED.
I would greatly appreciate a 'why', and more importantly, a 'how to fix it' for this problem.
If you need to look at the code, a NOTEPAD file is attached.
Much appreciated
--start trigger-- create trigger UpdateAffiliateEarnings on Orders for insert, update as
--check existence of affiliateid, and for product type select @AffID=AffID, @ProductType=Source from Orders where AffID IS NOT NULL
--get relevant information if @AffID IS NOT NULL begin if @ProductType = 'FLOWER' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,FlowerOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end if @ProductType='PHONE' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,PhoneOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end
--update Affiliate account --get totals to update select @CurrentEarnings=AffTotalEarnings, @AffTotalPayments=AffTotalPayments from Affiliates where AffID=@AffID
--calculate new totals for affiliate account set @AffTotalEarnings=@CurrentEarnings+@Earnings set @AffOutstandingBalance=@AffTotalEarnings-@AffTotalPayments
--update affiliate account to new totals update Affiliates set AffTotalEarnings=@AffTotalEarnings, AffOutstandingBalance=@AffOutstandingBalance where AffID=@AffID
--roll back the transaction if there is an error if @@ERROR !=0 rollback tran end -- end of trigger --
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.
[code = "sql"]CREATE TRIGGER Trigger_20321 ON FACT_CUST_GRP_ICM_MO AFTER DELETE AS /* DELETE trigger on FACT_CUST_GRP_ICM_MO */ /* default body for Trigger_20321 */
[Code]...
Msg 102, Level 15, State 1, Procedure Trigger_20321, Line 21 Incorrect syntax near ')'.
I have the following trigger that updates a couple test fields to null when they are 1/1/1900, works great on inserts, and one line updates:
CREATE TRIGGER UpdateDate ON test AFTER INSERT, UPDATE AS UPDATE Test SET [CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END, [CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END where AutoID = (select AutoID from inserted)
However, when trying to do a multi line update statement, I get the following error:
Msg 512, Level 16, State 1, Procedure UpdateDate, Line 7
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
I need a trigger (well, I don't *need* one, but it would be optimal!)but I can't get it to work because it references ntext fields.Is there any alternative? I could write it in laborious code in theapplication, but I'd rather not!DDL for table and trigger below.TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblMyTable]GOCREATE TABLE [dbo].[tblMyTable] ([fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTableFOR INSERTASBEGINIF UPDATE(fldKBSubject)BEGINUPDATEtblMyTableSETfldSubject = i.fldKBSubjectFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDIF UPDATE (fldKBDescription)BEGINUPDATEtblMyTableSETfldDescription = i.fldKBDescriptionFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDEND
I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
I'm stumped on this trigger error and I'm hoping someone can help. Here's the background:
BACKGROUND Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error.
PROBLEM I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error:
SQLDBCode: 229Alerts error: SQLSTATE = 4200Microsoft OLE DB Provider for SQL ServerEXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'...
TROUBLESHOOTING I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do.
We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong)
This problem is driving me crazy so I thank you in advance for any suggestions!
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391
A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger': System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. Changed database context to 'pims'. OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ". System.Data.S (Source: MSSQLServer, Error number: 6549) Get help: http://help/6549
I've just noticed some strange behavior that seems like a bug to me. It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause: -- DROP TABLE TestTable CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one: -- DROP VIEW TestView CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field: INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt: -- DROP TRIGGER TestTrigger CREATE TRIGGER TestTrigger ON TestView INSTEAD OF INSERT AS BEGIN INSERT INTO TestTable (TestField0, TestField1) SELECT TestField0, COALESCE(TestField1, 'X') FROM inserted END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error: INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1 The column 'TestField1' in table 'TestView' cannot be null.
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen
I have a c# stored procedure that is being called from a trigger. When I execute it from management studio, it works just fine. But, when I update a record in the table that has the trigger that calls the sp, I get the error "transaction context in use by another session error". I've tried a few of the "fixes" that I found through searching, but so far nothing seems to work. What I've tried so far is...
Removing the transaction from my code making sure my code is only using 1 connection setting XACT_ABORT ON
Thanks for your time: Form collects 30 different categories of data as 1 row into a table. Then this After Insert Trigger inserts unique rows into another table per "if logic statements" for each of the 30 scenarios (see examples below).
How do I BEST apply TryCatch and @@error feedback against the following to minimize risk of lost insert statements, AND LOG their details for follow-up:
tr_A_Form_I_ItemRows CREATE TRIGGER trg_insertItemRows ON dbo.A_Form AFTER INSERT AS IF ( @@ROWCOUNT = 0 ) RETURN SET NOCOUNT ON
-- 1 of 15 Checkbox Driven [Missing WORKSHEET is Valued]: IF (select distinct i.Fieldname from inserted i) = 1 BEGIN Insert into... Values(...) END
-- 1 of 8 Textbox Driven [REPLACE PATIENT NAME is valued]: IF (select distinct i.C_PtName from inserted i)IS NOT NULL BEGIN Insert into... Values(...) END
Hi Everyone, I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter. What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:
Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
I am using SQL Management Studio Express, and SQL Server Express.
Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?
Code Block
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of -- the row being inserted> -- ============================================= CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger ON dbo.dbCG_Disposition AFTER INSERT AS BEGIN
DECLARE @rowsAffected int, @msg varchar(2000), --for error message @BouleID varchar(6) -- and do I need to enter more than one?
SET @rowsAffected = @@rowcount
IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time. -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON SET ROWCOUNT 0
BEGIN TRY
--VALIDATION BLOCK Leave this alone for now
SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.
UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1 SET Iter = ( Iter + 1 ) -- WHERE BouleID = @BouleID --
END TRY BEGIN CATCH IF @@TRANCOUNT >0 ROLLBACK TRANSACTION
--or this will get rolled back EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.
DECLARE @ERROR_MESSAGE nvarchar(4000) SET @ERROR_MESSAGE = ERROR_MESSAGE() RAISERROR (@ERROR_MESSAGE, 16, 1) END CATCH
Not sure if there's a GP 8.0 forum, so giving this one a go.
I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB.
The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown:
"Unhandled database exception: A Save operation on table €˜RM_Customer_MSTR€™ failed accessing SQL data
EXCEPTION_CLASS_DB DB_ERR_SQL_DATA_ACCESS_ERR€?
The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record.
Code for the trigger follows:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE TRIGGER CTrig_Logic_Update_Customer ON RM00101
AFTER UPDATE AS
IF UPDATE(CUSTNMBR) or UPDATE(CUSTCLAS) or UPDATE(CNTCPRSN) or UPDATE(STMTNAME) or UPDATE(SHRTNAME) or UPDATE(TAXSCHID) or UPDATE(ADDRESS1) or UPDATE(ADDRESS2) or UPDATE(ADDRESS3) or UPDATE(CITY) or UPDATE(STATE) or UPDATE(ZIP) or UPDATE(PHONE1) or UPDATE(FAX) or UPDATE(SLPRSNID) or UPDATE(PYMTRMID) or UPDATE(PRCLEVEL) or UPDATE(SALSTERR) or UPDATE(INACTIVE) or UPDATE(HOLD) or UPDATE(CRLMTAMT)
BEGIN
DECLARE @Server_Name Varchar(25), @Logic_DB_Name Varchar(25), @SQLStr nvarchar(1000), @CustomerN int, @SICCode int, @ARContact Varchar(35), @LongName Varchar(50), @CustomerName Varchar(24), @SalesTaxCode Int, @AddrLine1 Varchar(40), @AddrLine2 Varchar(40), @AddrLine3 Varchar(40), @City Varchar(30), @StateProv Varchar(4), @PostalCode Varchar(15), @TelephoneN Varchar(25), @FaxTelephoneN Varchar(25), @SalespersonN Int, @TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field @CustRateSched Int, @SalesAreaCode Int, @InactivePurge Tinyint, @CreditStatus Tinyint, @CreditLimit Int
------- Get the new Customer data from Inserted table
SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer), @SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0), @ARContact = RTRIM(i.CNTCPRSN), @LongName = RTRIM(i.STMTNAME), @CustomerName = RTRIM(i.SHRTNAME), @SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0), @AddrLine1 = RTRIM(i.ADDRESS1), @AddrLine2 = RTRIM(i.ADDRESS2), @AddrLine3 = RTRIM(i.ADDRESS3), @City = RTRIM(i.CITY), @StateProv = RTRIM(LEFT(i.STATE,2)), @PostalCode = RTRIM(i.ZIP), @TelephoneN = RTRIM(LEFT(i.PHONE1,10)), @FaxTelephoneN = RTRIM(LEFT(i.FAX,10)), @SalespersonN = RTRIM(i.SLPRSNID), @TermsCode = RTRIM(i.PYMTRMID), @CustRateSched = RTRIM(i.DEX_ROW_ID), @SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0), @InactivePurge = i.INACTIVE, @CreditStatus = i.HOLD, @CreditLimit = i.CRLMTAMT FROM inserted i
------- Get Logic server name and database name
SELECT @Server_Name = RTRIM(l.Server_Name), @Logic_DB_Name = RTRIM(l.Logic_DB_Name) FROM tbl_Logic_DB l
------- Insert new Customer record into Logic database
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
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'
