Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28

    Unanswered: Using a 'ntext' variable in a trigger

    Hello, I have a very simple trigger that only copies the data from one table to a historial table, when a row is deleted. It works fine, but now I need to change it becouse I am inserting a new column to the table, which type is ntext, and when I try to declare a ntext variable (inside the trigger), it does not allow me. Does anyone know how to go round this? Here is the code of the trigger (maybe it is not efficient, as I am a newbie in this , please tell if so as well)

    Code:
    CREATE TRIGGER OnDelete ON dbo.Tracker 
    FOR DELETE 
    AS
    
    declare @IDregistro as integer;
    declare @IDTIT as integer;
    declare @negocio as nvarchar(50);
    declare @fechaOr as datetime;
    declare @asuntoOr as nvarchar(50);
    declare @estadoOr as nvarchar(50);
    declare @descripcionOr as nvarchar(255);
    declare @usuarioOr as nvarchar(50);
    declare @contactoOr as nvarchar(50);
    declare @companiaOr as nvarchar(40);
    --declare @desc as ntext; --DOES NOT WORK!!
    
    declare @fechaSus as datetime
    
    select @fechaSus = getdate()
    
    select @IDregistro = IDregistro from deleted;
    select @IDTIT = IDTitular from deleted;
    select @negocio = negocio from deleted;
    select @fechaOr = fecha from deleted;
    select @asuntoOr = asunto from deleted;
    select @estadoOr =estado from deleted;
    select @descripcionOr = descripcion from deleted;
    select @usuarioOr = usuario from deleted;
    select @contactoOr = contacto from deleted;
    select @companiaOr = compania from deleted;
    
    insert into Historico (IDregistro, IDtitular, Negocio, FechaSuceso,TipoSuceso, FechaOriginal, AsuntoOriginal, EstadoOriginal, DescripcionOriginal, UsuarioOriginal, ContactoOriginal, CompaniaOriginal)  Values (@IDregistro, @IDTIT, @negocio, @fechaSus, 'DELETE', @fechaOr, @asuntoOr, @estadoOr, @descripcionOr, @usuarioOr, @contactoOr, @companiaOr)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't tried this, but I think you could use:
    Code:
    CREATE TRIGGER OnDelete ON dbo.Tracker FOR DELETE AS
    
    --declare @desc as ntext; --DOES NOT WORK!!
    
    INSERT INTO Historico (
       IDregistro, IDtitular, Negocio
    ,  FechaSuceso,TipoSuceso, FechaOriginal
    ,  AsuntoOriginal, EstadoOriginal, DescripcionOriginal
    ,  UsuarioOriginal, ContactoOriginal, CompaniaOriginal ) SELECT 
       IDregistro, IDTitular, negocio
    ,  GetDate(), 'DELETE', fecha
    ,  asunto, estado, descripcion
    ,  usuario, contaco, compania
       FROM deleted
    
    RETURN
    You'll need to put the description column in where it works for you. Note that this code should also fix the problem that prevented your old trigger from handling deletes of more than one row at a time too.

    -PatP

  3. #3
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28
    Nope, it stills throws back this error when I try to change the column type:

    "Error ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]No se pueden usar columnas de tipo text, ntext o image en las tablas insertadas y borradas."

    Which means something like "you can't use column type text, ntext or image in the inserted and deleted tables"

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooops. Sorry about that!

    Do you have a primary key defined (a column or group of columns guaranteed to be unique)? That would allow you to get the data from the underlying dbo.Tracker table using a sub-query (a SELECT statement within another SELECT statement).

    -PatP

  5. #5
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28
    Nop, don't get it... how is it that I'm gonna get the data from the deleted table? Could you give me an example? Thanks

  6. #6
    Join Date
    Dec 2004
    Posts
    1

    Smile Try this

    Check out this link http://doc.ddart.net/mssql/sql2000/h...es_08_6f3n.htm
    Hope it helps you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •