Trigger doesn't work in SQL Server 2005

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)

Trigger doesn't work in SQL Server 2005

Author

Topic

mikgri
Starting Member

39 Posts

Posted - 2007-05-23 : 12:23:02

I have a trigger that worked on SQL Server 7.0 after moving to SQL Server 2005 the same trigger doesn’t work.
Can anybody let me know why?
Below is trigger body and stored procedure.

CREATE TRIGGER [dbo].[Trigger_MP_96]
on [dbo].[MP193944198]

FOR INSERT, DELETE

AS

declare @SQL varchar(4000), @MediaPlanID int , @MpTablename varchar(128) , @McTablename varchar(128),@DBName varchar(128)

select @MediaPlanID = cast(replace( name,'Trigger_MP_','') as int) from sysobjects where id = @@procid

select @MPTableName =tablename , @MCTableName = mctablename
from mediaplanref where mediaplanid = @MediaPlanID

select @DBName = db_name()
execute EM_master.dbo.spEM_UpdateMPStoreProductCount @MPTableName, @MCTableName,@MediaPlanID, @DBName

------------------------------------
create procedure [dbo].[spEM_UpdateMPStoreProductCount]
(
@MPTableName varchar(128) ,@MCTableName varchar(128) , @MediaPlanID int,
@Databasename varchar(128)
)
as
set nocount on
declare @SQL nvarchar(4000)
set @SQL = '
use %CData
update mediaplanref set scount = b.scount,pcount =b.pcount
from
(
select scount =count(distinct storeid) ,pcount= count (distinct productid )
from (
select distinct productid ,storeid from %MPTable%
union
select distinct productid ,storeid from %MCTable%
) A
) b , mediaplanref a where a.mediaplanid = @MediaPlanID
'

set @SQL = Replace(@SQL,'%MPTable%', @MPTableName )
set @SQL = Replace(@SQL,'%MCTable%', @MCTableName )
set @SQL = Replace(@SQL,'%CData', @Databasename )

exec sp_executesql @SQL,N'@MediaPlanID int',@MediaPlanID

Thanks,
mikgri.

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 13:20:59

sysobjects is kinda different under SQL 2005, isn't it?

Have you tried running the SQL 2005 Upgrade Advisor on your SQL 7 database and seeing what it recommends?

Kristen