473,699 Members | 2,377 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with INSERT TRIGGER - fails with error.

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY( id, N'IsUserTable')
= 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifie r ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBDescriptio n] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFi eldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN
IF UPDATE(fldKBSub ject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID

END

IF UPDATE (fldKBDescripti on)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescript ion
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID
END
END

Mar 17 '06 #1
3 3725
On 17 Mar 2006 06:24:01 -0800, te********@hotm ail.com wrote:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.
Hi Edward,

Thanks for providing the DDL!

I'll come to your problem later, but first some comments.
CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifie r ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBDescriptio n] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.

After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.

Does the fldSCID column really have to be uniqueidentifie r? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifie r are very rare.

Apart from the uniqueidentifie r column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.

Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.

On to the trigger (I removed the empty lines for readability)
CREATE TRIGGER PrepopulateKBFi eldsFromQuery ON dbo.tblMyTable
FOR INSERT
AS
BEGIN

IF UPDATE(fldKBSub ject)
BEGIN
UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID
END

IF UPDATE (fldKBDescripti on)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescript ion
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID
END
END
In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.

There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.

But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?

Anyway, back to your question:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.


You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFi eldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go

--
Hugo Kornelis, SQL Server MVP
Mar 17 '06 #2
Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:
After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.
I would guess that one of the subjects are intended to be a key of some
sort, but since it's probably a free-text column, a PK/UNIQUE constraint
only gives you half protection, as it will not catch variations due to
typos and spaces.
Does the fldSCID column really have to be uniqueidentifie r? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifie r are very rare.
Unless you are into replication. GUIDs are also popular among web
programmers, because they can save a roundtrip to get the key value.
I've seen more than one URL with GUIDs in them.
You might also want to rethiink the choice of ntext/nvarchar over
text/varchar - unless you really need to store characters from
non-Western alphabets or other characters that are only available in
unicode, there's no reason to use double the space taken.
Not sure I agree. The cost for a change when a requirement to support,
say, Japanese, comes can prove to be prohibitive.
But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?


The trigger name says "prepopulat e". I guess Edward is setting an initial
default.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 17 '06 #3

Hugo Kornelis wrote:
On 17 Mar 2006 06:24:01 -0800, te********@hotm ail.com wrote: [...]
CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifie r ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldKBDescriptio n] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.


Weird, it IS the PK! An error with the script, or maybe I was a bit
eager on the editing.
Does the fldSCID column really have to be uniqueidentifie r? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifie r are very rare.
At one time the database was replicated.
Apart from the uniqueidentifie r column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.
Couldn't agree more - not my DB design!
Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.
The ntext come from the Access upsizing wizard. The original designer
simply left the default values (it would have been memo columns in
Access)
On to the trigger (I removed the empty lines for readability)
CREATE TRIGGER PrepopulateKBFi eldsFromQuery ON dbo.tblMyTable
FOR INSERT
AS
BEGIN

IF UPDATE(fldKBSub ject)
BEGIN
UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID
END

IF UPDATE (fldKBDescripti on)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescript ion
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldC SID
END
END
In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.


Yes, I realise that now - thanks.
There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.
I tend to be very "belt and braces" with my code.
But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?
I want to do it because the underlying application is a query system.
Some queries will form part of a Knowledge Base system. The client
wants the (QUERY)Subject and Description columns to be mirrored by the
KBSubject and KBDescription fields, at least initially. Only the KB
versions will be exposed to the customer.
Anyway, back to your question:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.


You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFi eldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go


And that is absolutely spot on! Many thanks.

Edward

Mar 20 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1676
by: Attiq ur Rehman | last post by:
Hello All, I'm trying to write a stored procedure which tracks the insert event in a Table A. When we insert something in Table A in Database 'FMDB', it inserts the same information in Table B in Database 'Wartung'. When I insert something with insert query visa SQL Query analyzer, it works fine. If i insert records via my C++ programme, it gives error that
3
1764
by: Curtis Gilchrist | last post by:
I'm trying my hand at triggers and it doesn't seem to be working for me. I have a very simple database that consists of one table: Employees. I want to create a trigger that will limit the EMP_TITLE field to either Ms., Mr., or Mrs. I am using the following code: CREATE trigger triTitleCheck ON employee FOR insert, update AS declare @v1 varchar
9
3456
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
18
5981
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such as (without the quotes)... " where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...
7
9709
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
4
2352
by: Alexis | last post by:
Hello, I'm facing oracle trigger problem. Anyone can help or advise how to resolve it? Below are the explaination on my problem I've created a trigger for my program. When there is a new data insert into table moto_pvs_pulse_daily_yield_tmp, my program will checking and copy the data by each row and insert into table mt_pulse_daily_yield_tmp which is in other instance. After I success insert into table mt_pulse_daily_yield_tmp, I...
11
7872
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist drop table log_errors_tab;
3
1634
by: faathir88 | last post by:
i'd like to insert lots of data n its hard to determine which field would be the primary key, coz all of them almost similar. So, i decided to use sequence for its PK by using trigger here's the code : Create or replace trigger bef_ins_primer before insert on dpsj_primer for each row begin insert into dpsj_primer...
1
1450
by: ITHELP85 | last post by:
using SQL Server 2005 I have a table SALES_ITEM, users should be able to input the Primary Keys (ItemNumberSK and InvoiceNumber), and Qty. I want the ItemName and UnitPrice fields to update automatically from the WAREHOUSE table. I wrote a trigger already but something is wrong with it as I get this error (My code is listed under the error): --------------------------- Microsoft SQL Server Management Studio ---------------------------...
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8621
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9042
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8891
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7759
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6538
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2357
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.