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] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
FOR INSERT
AS
BEGIN
IF UPDATE(fldKBSubject)
BEGIN
UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
END 3 3695
On 17 Mar 2006 06:24:01 -0800, te********@hotmail.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] uniqueidentifier ROWGUIDCOL NOT NULL , [fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldKBDescription] [ntext] COLLATE SQL_Latin1_General_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 uniqueidentifier? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifier are very rare.
Apart from the uniqueidentifier 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 PrepopulateKBFieldsFromQuery ON dbo.tblMyTable FOR INSERT AS BEGIN
IF UPDATE(fldKBSubject) BEGIN UPDATE tblMyTable SET fldSubject = i.fldKBSubject FROM inserted i INNER JOIN tblMyTable ON i.fldCSID = tblMyTable.fldCSID END
IF UPDATE (fldKBDescription) BEGIN UPDATE tblMyTable SET fldDescription = i.fldKBDescription FROM inserted i INNER JOIN tblMyTable ON i.fldCSID = tblMyTable.fldCSID 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 PrepopulateKBFieldsFromQuery
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
Hugo Kornelis (hu**@perFact.REMOVETHIS.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 uniqueidentifier? If you choose to use surrogate keys, then IDENTITY should be the regular choice; situations that call for uniqueidentifier 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 "prepopulate". I guess Edward is setting an initial
default.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Hugo Kornelis wrote: On 17 Mar 2006 06:24:01 -0800, te********@hotmail.com wrote:
[...]CREATE TABLE [dbo].[tblMyTable] ( [fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL , [fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [fldKBDescription] [ntext] COLLATE SQL_Latin1_General_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 uniqueidentifier? If you choose to use surrogate keys, then IDENTITY should be the regular choice; situations that call for uniqueidentifier are very rare.
At one time the database was replicated.
Apart from the uniqueidentifier 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 PrepopulateKBFieldsFromQuery ON dbo.tblMyTable FOR INSERT AS BEGIN
IF UPDATE(fldKBSubject) BEGIN UPDATE tblMyTable SET fldSubject = i.fldKBSubject FROM inserted i INNER JOIN tblMyTable ON i.fldCSID = tblMyTable.fldCSID END
IF UPDATE (fldKBDescription) BEGIN UPDATE tblMyTable SET fldDescription = i.fldKBDescription FROM inserted i INNER JOIN tblMyTable ON i.fldCSID = tblMyTable.fldCSID 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 PrepopulateKBFieldsFromQuery 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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'...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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,...
|
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...
| |