|
Hi,
I am trying to add a trigger on insert event on a table in SQL_Express.
ALTER trigger [dbo].[TR_Insert_New_Pair] on [dbo].[Vendors]
FOR INSERT AS
DECLARE
@ACCPACID varchar(12),
@VNAME varchar(60)
SELECT
@ACCPACID = (SELECT ACCPACVENDORID from inserted),
@VNAME = (
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
)
UPDATE dbo.Vendors SET
VENDNAME = @VNAME
WHERE
LTRIM(RTRIM(ACCPACVENDORID)) = ltrim(rtrim(@ACCPACID))
--This part works great (I've tested)
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
Is there something I am not seeing ????
Thanks
Kirill
|
|
|
|
|
zeusToronto wrote: Is there something I am not seeing ????
I'm not seeing your question. What the problem and error message?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am sorry,
when Insert happens trigger does not seem to update the line. This trigger has other parts to it which seem to work just fine.
|
|
|
|
|
Have you checked that the value from the linked server is being populated? Have you tried the simple test of hard coding a value into @Vname and seeing if that works? I have a sneaky feeling that linked servers don't work from triggers, but I'm not certain.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield this is exactly how I've tested everything. Values do come from linked server, and when I hard code the value trigger seem to update it just fine.
I even went as far as changing the trigger to update from the local table (LOCAL SQL Table) and it works. The only issue I am having is when update happens from a linked server.
|
|
|
|
|
zeusToronto wrote: Values do come from linked server
Is that tested from within the trgger or just as a piece of sql? Based on your reply, if you hard code it or get the values from a local table it works, the only time it doesn't is when you get the values from a linked server in the trigger. The only cnclusion you can draw from this is that the linked server fetch is not working in the trigger.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi everybody,
I'm getting this error.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Contract_ContractType". The conflict occurred in database "ContractDB", table "dbo.ContractType", column 'ContractTypeId'. The statement has been terminated.
I understand that this is because I'm not sending the exact ContracatTypeId to table. I'm doing this deliberately because I want to store Null as well for some reason.
Please could any body tell me how can I remove/drop this constraint from database (please bare in mind I have no table diagram).
any help will be much appreciated.
|
|
|
|
|
just goto your detail table where your Foreign Key exists , open the table and click on your field which is Foreign Key and right click and click on Relationships , select the specific relationship and on the right side Grid in table designer Set the value for 'Enforce Foregin Key Constraint' to 'No'
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
|
Hello Friends,
I've two columns i.e Name and Address
Now i want to swap the column names i mean Name to be Address and Address to be Name
|
|
|
|
|
And the question is...?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Just go to your table and Rename the table fields in Design View , if you want to do it script then you can click on Generate Change Script and it will Create Script for your
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
As the others said, plus you may want to use a third and temporary name to avoid a "Field name already used" error (like when swapping two variables).
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
Yes
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
how about
select Name as Address, Address as Name from table
Just create a view on it and start working ..
|
|
|
|
|
Hello every body!
I am writing a C# application using SQL Server as a database. I run transactions on some tables of my database. I want to write something like a trigger for example, that if a transaction rollbacks, for whatever reason, the database itself tries it one more time and then send the response to the application. Is it possible or not? If yes, how can I do this?
Thanks in advance
|
|
|
|
|
Well any transaction is role backed by the database not by the Front end application because data within transaction does not full fill the criteria , Thats why is role backed.
It will not Commit the transaction unless and until it fulfills the criteria set whether you send this from front end or from back end
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Hi,
I have a table of product movements between places,
Table Movements
{fromId, toId, date, productId, status, firmware, etc}
And a table of all the products
Table Products
{productId, name, status, firmware, etc}
My question is if its OK this design, in particular the bolded things.
In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table..
So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table.
Another solution could be to have a table
Table ProductProperties
{productPropertiesId, status, firmware}
and the other two would be like:
Table Movements
{fromId, toId, date, productId, productPropertiesId}
Table Products
{productId, name, currentPropertiesId}
Any comments?
|
|
|
|
|
It's a little confusing...
At what point does the Product table get updated to reflect its new status/firmware etc? Why does moving something from one place to another change these? Can a product exist that never moves?
|
|
|
|
|
The last movement associated to some product determines its status/firmware.
But yes, there might be products that havent been moved ever yet.
|
|
|
|
|
Quake2Player wrote: In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table..
So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table.
This is definitely the best solution.
The other solution would only create one extra table you would need to query, making your queries needlessly complicated.
My advice is free, and you may get what you paid for.
|
|
|
|
|
I probably wouldn't have status and firmware in the Product table. When a new Product created I'd create the first Movement (created, bought, whatever) record for that Product.
|
|
|
|
|
Oh thats a nice solution..
Though the movements are used in other parts of the software.. so this "dummy" movements would have to have different properties.. like "from = to"
|
|
|
|
|
You might instead have a dummy location for the from value, e.g. "thin air".
|
|
|
|
|
Dear All
I have a Table Named CustomerDetails where i have millions of records the table structure is as follows
RecordId CustomerName CompanyName EmailAddress Phone
Is it possible to get the EmailAddress count of specific domains like how many emails with @gmail.com, @yahoo.com, @rocketmail.com, @att.net, @verizon.net
I have tried a lot of ways but i have failed in all tries , If possible drop me a line of code so it will be a great help for me
Kind regards
James
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
