June 21, 2002 at 5:55 pm
I created a View that merges two tables. Basically, when I'm programming my .asp pages I only want to interact with the View. I do not want to deal with the inner workings. I set up a trigger on the View to fire on an INSERT command. What I want to do inside the trigger is split up the data and put it into its proper internal tables. The problem I'm running into is that I need to be able to store a TEXT value into my database. So far I have been doing something similar to the following:
DECLARE @myvalue INT
SELECT @myvalue = inputcol FROM Inserted
INSERT INTO InternalTable (internalcol) VALUES (@myvalue)
However, I cannot declare a local variable of type TEXT. I've tried using textptr() and WRITETEXT but I can't get it to work. I've been all over msdn and I can't find a similar example. Is there a simple way of doing this?
June 24, 2002 at 1:31 am
I'm not that clear as to what you're trying to do. Are you talking about Text as nText etc or a varchar.
Just a thought, You might find it more certain and useful to use a stored procedure to do the data-distribution rather than updating from the view. Triggers in SQL server only fire on the last record in a batch
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 24, 2002 at 5:53 am
Text is hard to work with. You do need to remember that the trigger will fire per set, not per row, so unless you're going to be doing something to each row, you won't need a variable. If you have SQL2K, an instead of trigger is a great way to handle the situation where you're updating a view that needs to update more than one underlying table.
Andy
June 24, 2002 at 10:07 am
I am running SQL2K and I am using an INSTEAD OF trigger to handle an INSERT INTO a View. What I want is the view to act just like a table that I can INSERT, DELETE and UPDATE to. One if the fields is of TEXT data type. Let me be a little more specific with what I'm trying to do.
I am currently setting up a database for the university I work at that will hold the content of the university's course catalog (a listing of all courses offered, with course descriptions). I found that all the fields fit nicely into normal inrow data types except for the course description. The length of the course description varies quite a bit and can be very large, so I thought it would be best kept in a TEXT data type.
Not only does the database need to hold the current information, it needs to keep track of all changes. There needs to be functionality to rebiuld the course catalog as it was on a certain date. To do this, I did the following:
I have a table that holds all of the course entries, even if they are outdated. Nothing ever gets deleted from this table. I have a table that is nothing but links to the current courses so that I can produce the current catalog with one quick join. The third table is a log of all changes made to the database, so that I can step through each entry and rebuild the catalog as it was on a specific date.
You can see my design diagram at http://www.uml.edu/weboffice/sgordon/catalog/design/database.html. I'm only referring to the catalog section. You can ignore the Degree Program tables and views.
Instead of having to do many complicated SQL calls from within my asp, I decided to create views that I can use in my asp to manage the database. The CurrentCourses view holds a table join between the CurrentCoursesLinks table and the Courses table so that I am returned a full table of all of the current courses. What I want to do is to support an INSERT statement into the CurrentCourses view. I created a trigger that is fired as an INSTEAD OF INSERT. All I need to do in this table is to first create a new entry in Courses and pass along all the inputs. Second, I get the ID of the newly created Course entry and insert it into the CurrentCoursesLinks table. I have triggers on CurrentCoursesLinks that take care of the CourseHistory table.
I can do all of the above except for one thing. The coursedescription field is of type TEXT. Inside my trigger, I can not find a way to pass the value of coursedescription into the Courses table because it will not allow me to put the value of coursedescription into a local variable. I've tried such calls as INSERT INTO Courses (coursedescription, ...) VALUES (coursedescription FROM Inserted, ...) but it will not allow me to do this. Any help?
June 26, 2002 at 1:56 pm
I found the answer to my problem. It turns out I can do this in one simple sql statement.
INSERT INTO Courses ([coursecategorynumber], [coursenumber], [coursetitle], [coursedescription], [coursecredits])
VALUES (SELECT coursecategorynumber, coursenumber, coursetitle, coursedescription, coursecredits FROM Inserted)
Edited by - squallgmn on 06/26/2002 1:57:11 PM
Edited by - squallgmn on 06/26/2002 1:57:34 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply