有谁能帮个忙呢?解决马上结贴

Question

試試吧!
語法差不多
可能需要調試CREATE TRIGGER   test   ON  classinfo_tmp
FOR INSERT
AS
declare    @v_cnt   smallint
select  v_cnt=count(*)     from      where class_seq= inserted.class_seq
  if v_cnt=0  
     begin
     insert into classinfo(class_seq,
                           class_type,
                           class_name,
                           manager)
                    values(  inserted.class_seq,
                            inserted.class_type,
                            inserted.class_name,
                            inserted.manager
                          )
    end
  else
    begin
     update classinfo set class_type=inserted.class_type,
                          class_name=inserted.class_name,
                          manager=inserted.manager
       where class_seq=inserted.class_seq
    end
    
SQL Server  Tigger Example: 
USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'employee_insupd' AND type = 'TR')
   DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
SELECT @min_lvl = min_lvl, 
   @max_lvl = max_lvl, 
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
   JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
END

Answer 1

try:create trigger tri_classinfo_tmp
on classinfo_tmp 
after insert
as
begin
 declare @v_cnt intset @v_cnt=( select count(*) c  from classinfo c,inserted i
    where c.class_seq=i.class_seq)
  if @v_cnt=0 
     insert into classinfo(class_seq,
                           class_type,
                           class_name,
                           manager)
                   select class_seq,class_type,class_name,manager from inserted
                        
     update classinfo set class_type=i.class_type,
                          class_name=i.class_name,
                          manager=i.manager from inserted i
       where class_seq=i.class_seq
 
    end

Answer 2

CREATE TRIGGER   test   ON  classinfo_tmp
FOR INSERT
AS
declare    @v_cnt   smallint
select  v_cnt=count(*)     from  ClassInfo    where class_seq= inserted.class_seq
  if v_cnt=0  
     begin
     insert into classinfo(class_seq,
                           class_type,
                           class_name,
                           manager)
                    values(  inserted.class_seq,
                            inserted.class_type,
                            inserted.class_name,
                            inserted.manager
                          )
    end
  else
    begin
     update classinfo set class_type=inserted.class_type,
                          class_name=inserted.class_name,
                          manager=inserted.manager
       where class_seq=inserted.class_seq
    end
服务器: 消息 128，级别 15，状态 1，过程 test，行 12
在此上下文中不允许使用 'class_seq'。此处只允许使用常量、表达式或变量。不允许使用列名。

Answer 3

SQL Server怎么就这么难用呀,我们用Oracle一下子就可以搞定!

Answer 4

可以使用Table Name 做為UPDate 的值
語法:
           update classinfo set classinfo.class_type=inserted.class_type,
                          classinfo.class_name=inserted.class_name,
                          classinfo.manager=inserted.manager
           from  inserted                          ----新加
           where classinfo.class_seq=inserted.class_seq
另外:
          在Sql Server 中 的變量必須以@ 開頭試試看!