SQL 7.0 Triggers - 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 2000 Forums

SQL Server Development (2000)

SQL 7.0 Triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-10 : 09:26:27

Sonny writes "I have some triggers in place to update tables to a remote sql server. The triggers work while there is only one row to insert or update, but if there are more than one it fails.

Here is one of the triggers

CREATE trigger trg_textreq_univ_dept_add on univ_dept
for insert, update

as

declare @count int
DECLARE @SITE_ID int
SET @SITE_ID = 201

-- Perform inserts/updates
select @count = count(*) from inserted
if @count > 0
begin

DECLARE @CAMPUS_ID int
DECLARE @DEPT_NAME varchar(255)
DECLARE @DEPT_ABREV varchar(25)
DECLARE @DFLT_BO_DEPT_ID varchar(50)
DECLARE @DEPT_ID INT
DECLARE @DEPT_DESC varchar(5000)
set @CAMPUS_ID = 12

DECLARE i_cursor cursor for
select DEPT_ID, DEPT_NAME, DEPT_ABREV, BACKOFFICE_DEPT_ID from inserted

open i_cursor
fetch next from i_cursor into @DEPT_ID, @DEPT_NAME, @DEPT_ABREV, @DFLT_BO_DEPT_ID

while (@@fetch_status=0)
begin
set @DEPT_DESC = (select cast(dept_description as varchar(5000)) from univ_dept where dept_id = @DEPT_ID)
exec remote_sql_server.textreq.dbo.sp_univ_dept_add @SITE_ID, @DEPT_ID, @CAMPUS_ID, @DEPT_NAME, @DEPT_ABREV, @DEPT_DESC, @DFLT_BO_DEPT_ID

fetch next from i_cursor into @DEPT_ID, @DEPT_NAME, @DEPT_ABREV, @DFLT_BO_DEPT_ID
end

close i_cursor
deallocate i_cursor

end

Thanks
Sonny"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2001-12-16 : 07:00:18

Hi Sonny,

Try this one
No need to use Cursor.

CREATE trigger trg_textreq_univ_dept_add on univ_dept
for insert, update
as

declare @count int
DECLARE @SITE_ID int
SET @SITE_ID = 201
-- Perform inserts/updates
--select @count = count(*) from inserted
--if @count > 0
--begin

DECLARE @CAMPUS_ID int
DECLARE @DEPT_NAME varchar(255)
DECLARE @DEPT_ABREV varchar(25)
DECLARE @DFLT_BO_DEPT_ID varchar(50)
DECLARE @DEPT_ID INT
DECLARE @DEPT_DESC varchar(5000)
set @CAMPUS_ID = 12

--DECLARE i_cursor cursor for
Select @DEPT_ID=DEPT_ID, @DEPT_NAME=DEPT_NAME, @DEPT_ABREV=DEPT_ABREV, @DFLT_BO_DEPT_ID=BACKOFFICE_DEPT_ID from inserted

--open i_cursor
--fetch next from i_cursor into @DEPT_ID, @DEPT_NAME, @DEPT_ABREV, @DFLT_BO_DEPT_ID

--while (@@fetch_status=0)
--begin
set @DEPT_DESC = (select cast(dept_description as varchar(5000)) from univ_dept where dept_id = @DEPT_ID)
exec remote_sql_server.textreq.dbo.sp_univ_dept_add @SITE_ID, @DEPT_ID, @CAMPUS_ID, @DEPT_NAME, @DEPT_ABREV, @DEPT_DESC, @DFLT_BO_DEPT_ID

--fetch next from i_cursor into @DEPT_ID, @DEPT_NAME, @DEPT_ABREV, @DFLT_BO_DEPT_ID
--end

--close i_cursor
--deallocate i_cursor

--end

sonmanvb
Starting Member

6 Posts

Posted - 2002-01-03 : 09:37:33

Thanks, that did it.
:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources