Insert, Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert, Update Trigger

Author

Topic

frankd
Starting Member

1 Post

Posted - 2008-12-12 : 08:11:52

I created an Insert, Update trigger in SQL Server 2005. The trigger checks that the user enters a valid county based on the zip_code. When only the county field is updated it works. However, when updating another cloumn or Inserting a new record I get the following error:

Imtransaction.rollback
Connectivity error: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Any suggestions?
Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER tr_ps_bill_cnty_ins_upd ON provsite
FOR INSERT, UPDATE

AS
BEGIN
SET NOCOUNT ON;
update p
set p.bill_cnty = z.county
from provsite p
inner join zipdemo z on z.zip_code = ltrim(substring(p.bill_zip,1,5))
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:26:49

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

so you want to check if county is valid county? which master table contain county details along with zipcode?

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-07 : 00:51:31

Hi Dear,

Please let me know how many records are available in the table provsite while Inserting or Updating.

Regards
Thiyagarajan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources