Trigger
-
I need your expertise help:
the user enter a new record via the front end application create in VB/Visual C++. This new record is inserted to table A. what i need to accomplish is to create a trigger when a new record is inserted into table A, i want to move the new record to table B. after the move is complete successfully, delete the new record from table A. how do i create this trigger and how do i invoke the trigger. your help is greatly appreciated.
-
It sounds like you might want to look into views. For lots of information on both see Books Online.
Noel
-
I don't see how a view would accomplish this!
A simple trigger is definitely what you need! It would be something like this,
CREATE TRIGGER TrgInsDelRecord
ON Address
FOR INSERT, DELETE
AS
BEGIN
INSERT Address2
SELECT * FROM INSERTED
DELETE Address
WHERE AddressID = (SELECT AddressID
FROM INSERTED)
END
-
Let me give you an example of a situation in which a view is better than a trigger.
There are two applications. Application X is a legacy app that uses table A. Application Y is a new app that utilizes a new set of tables including table B. Table A and table B will contain the same data and both application X and Y use this data. Application X’s authors have long since disappeared and the source code is lost so X cannot be modified to hit table B. The solution? Create a view on table B named A.
I have no idea what CrystalVis’ situation is, but I don’t think that you can be certain that she/he needs a trigger. My reason for suggesting looking at triggers AND views was so that she/he would be able to make the best decision. IMHO, one should always look for alternatives to triggers.
Noel
-
If you are using SQL2K I would use an instead of trigger on table A IT would look something like this:
CREATE TRIGGER
InsteadTrigger on TableA
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TableB
SELECT cols
FROM inserted
END
In this situation the data is not inserted into TableA so you do not have to go back and delete it, the data is inserted into TableB
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
You're both right & now have the requestor re-thinking the purpose for his question...
I would like to know why he/she needs to do this action and if it is a fast fix to yet another problem???
Regards,Coach James
-
Thank you all for your help/suggestion.
MrSQL, i create test table and apply your code it work perfectly. I'll have to do further testing and will report my finding. i was tasked to research this and not even clear why this action is needed. i'll have a meeting with the requestor and definitely gather all the details of why we need to accomplish this action and post the answer here. Again, thank you all for your input. i find this forum very valuable and learn tremendously everytime.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply