当我使用过程 GETDATE 时，SQL Server 2008 触发器抛出错误

发布于 2024-11-28 23:22:21 字数 995 浏览 2 评论 0原文

我正在尝试在 SQL Server 2008 中创建一个触发器，该触发器在第一个数据库中更新后将一行插入到第二个数据库中。

但是我不断收到错误..

（过程behaviour_alert，第11行'）'附近的语法不正确

这是因为在触发器中使用了DATETIME。

这可以作为普通查询使用，我不明白为什么它不能作为触发器使用。只能调整查询选择当前日期时间吗？

下面的查询

create trigger behaviour_alert
on [database1].[dbo].[studconduct]
for update
as
begin

declare @conductdatetime as datetime
set @conductdatetime = GETDATE()

insert into database2.dbo.behaviouralert
  select *
  from studconduct
  where curr_ind='Y'
  and cond_pts >= '5'
  and conduct_date >= @conductdatetime

我在这里缺少什么，斗鸡眼地看着这个。也许我喝太多咖啡了。

编辑：这就是我最终得到的结果并且它有效。我错过了触发器末尾的 END

create trigger behaviour_alert
    on [database1].[dbo].[studconduct]
    for update
    as
    begin
    insert into database2.dbo.behaviouralert
      select *
      from studconduct
      where curr_ind='Y'
      and cond_pts >= '5'
      and conduct_date >= datetime
    end

原文

I am trying to create a trigger in SQL Server 2008 which inserts a row into a 2nd database after an update in the 1st database.

However I keep getting an error..

(Procedure behaviour_alert, Line 11 Incorrect syntax near ')'

Is this because of using DATETIME in the trigger.

This works as a normal query, I can't see why it won't work as a trigger.
Can only adjust the query to choose the current datetime?

Query below

create trigger behaviour_alert
on [database1].[dbo].[studconduct]
for update
as
begin

declare @conductdatetime as datetime
set @conductdatetime = GETDATE()

insert into database2.dbo.behaviouralert
  select *
  from studconduct
  where curr_ind='Y'
  and cond_pts >= '5'
  and conduct_date >= @conductdatetime

What am I missing here, going crossed eyed looking at this. Maybe I have had too much coffee.

Edit: this is what I ended up with and it worked. I missed the END at the end of the trigger

create trigger behaviour_alert
    on [database1].[dbo].[studconduct]
    for update
    as
    begin
    insert into database2.dbo.behaviouralert
      select *
      from studconduct
      where curr_ind='Y'
      and cond_pts >= '5'
      and conduct_date >= datetime
    end

分享到QQ

分享到微博