BLOB (Text) flied in Trigger  
Author Message
capodles





PostPosted: Wed Feb 23 08:50:30 CST 2005 Top

SQL Server Developer >> BLOB (Text) flied in Trigger

Hi guys,
I have a table the contains a "text" field, I create a trigger on it to copy
the inserted record to anther indenticial table
The trigger code is:

CREATE TRIGGER Newevent ON dbo.CAL
AFTER INSERT
AS




insert into NEWCAL2
select * from CAL where


This works fine for all fields except the NOTES field -- which is of type
TEXT... i.e., all the data is copied across fine on insert except the NOTES.

SQL Server233  
 
 
Adam





PostPosted: Wed Feb 23 08:50:30 CST 2005 Top

SQL Server Developer >> BLOB (Text) flied in Trigger Your trigger looks fine for inserting the text column -- you're inserting
from the base table instead of the inserted table, which is the important
thing to do. I'm not sure why you only want a single row, though? I would
re-write it as:

CREATE TRIGGER Newevent ON dbo.CAL
AFTER INSERT
AS
insert into NEWCAL2
select * from CAL where
RECID IN (SELECT RECID FROM Inserted)


... I would also get rid of the SELECT * and use a column list instead...

But none of that is related to your actual problem. Can you post DDL and
some sample data to reproduce the issue?



--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--




> Hi guys,
> I have a table the contains a "text" field, I create a trigger on it to
copy
> the inserted record to anther indenticial table
> The trigger code is:
>
> CREATE TRIGGER Newevent ON dbo.CAL
> AFTER INSERT
> AS
>


>
> insert into NEWCAL2
> select * from CAL where

>
> This works fine for all fields except the NOTES field -- which is of type
> TEXT... i.e., all the data is copied across fine on insert except the
NOTES.
>
>
>


 
 
mike





PostPosted: Wed Feb 23 08:59:02 CST 2005 Top

SQL Server Developer >> BLOB (Text) flied in Trigger Hi

From BOL:
text, ntext, and image column references in inserted and deleted tables
After Triggers: Not Allowed
Instead of Triggers: Allowed

Regards
Mike



> Your trigger looks fine for inserting the text column -- you're inserting
> from the base table instead of the inserted table, which is the important
> thing to do. I'm not sure why you only want a single row, though? I would
> re-write it as:
>
> CREATE TRIGGER Newevent ON dbo.CAL
> AFTER INSERT
> AS
> insert into NEWCAL2
> select * from CAL where
> RECID IN (SELECT RECID FROM Inserted)
>
>
> .... I would also get rid of the SELECT * and use a column list instead...
>
> But none of that is related to your actual problem. Can you post DDL and
> some sample data to reproduce the issue?
>
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>


> > Hi guys,
> > I have a table the contains a "text" field, I create a trigger on it to
> copy
> > the inserted record to anther indenticial table
> > The trigger code is:
> >
> > CREATE TRIGGER Newevent ON dbo.CAL
> > AFTER INSERT
> > AS
> >


> >
> > insert into NEWCAL2
> > select * from CAL where

> >
> > This works fine for all fields except the NOTES field -- which is of type
> > TEXT... i.e., all the data is copied across fine on insert except the
> NOTES.
> >
> >
> >
>
>
>
 
 
Adam





PostPosted: Wed Feb 23 09:03:37 CST 2005 Top

SQL Server Developer >> BLOB (Text) flied in Trigger

>
> From BOL:
> text, ntext, and image column references in inserted and deleted tables
> After Triggers: Not Allowed
> Instead of Triggers: Allowed

Right. Did you look at the code? It's not selecting the text column
from the inserted table; it's only using the inserted table to get a key for
selecting from the base table.


--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--