当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - trigger in sql problem

问题描述:

this is my trigger

ALTER trigger [dbo].[addpay]

on [dbo].[pays]

after insert

as

declare @idtutor int

set @idtutor =(select idtutor from inserted)

begin

insert into pays (idtutor,nopay,datex,paythismonth)values (@idtutor,600,GETDATE(),'no')

end

but it doesn't add a new pays after inserted a tutor... i dont watch any bug, mistake, why doesn't it work

my tables

create table Tutor

(

[IdTutor] int primary key identity not null,

[Nombre] varchar(150) not null,

[ApellidoPaterno] varchar (150) not null,

[ApellidoMaterno] varchar (150) not null,

[EstadoCivil] varchar (10) not null,

[FechaNacimiento] varchar(50),

[Municipio] varchar(150) not null,

[Estado] varchar(150) not null,

[Direccion] varchar(250) not null,

[Sexo] varchar (9) not null,

[TelefonoTutor] char(10) not null,

[CelularTutor] char(15) not null,

[EmailTutor] char(50) not null,

[Empresa] varchar(150) not null,

[Ocupacion] varchar(250) not null,

[DireccionEmpresa] varchar (250) not null,

[TelefonoEmpresa] char(10) not null,

[CelularEmpresa] char(15) not null,

[EmailEmpresa] varchar(50) not null

)

create table pays

(

idpay int primary key not null identity,

idtutor int not null,

nopay float,

datex datetime,

paythismonth varchar(2)

)

网友答案:

You need to create the trigger on the table for where you want it to fire when a new record is inserted (Tutor in this case).

Additionally you need to remember that inserts/update statements can affect multiple rows so assigning to scalar variables won't work. The trigger you need is

CREATE TRIGGER YourTrigger
ON [dbo].[Tutor]
AFTER INSERT
AS
  BEGIN
      SET NOCOUNT ON

      INSERT INTO pays
                  (idtutor,
                   nopay,
                   datex,
                   paythismonth)
      SELECT idtutor,
             600,
             GETDATE(),
             'no'
      FROM   inserted
  END  

You will also need to drop the other trigger in your question with DROP TRIGGER [dbo].[addpay]

分享给朋友:
您可能感兴趣的文章:
随机阅读: