16,016,134 members
Sign in
Sign in
Email
Password
Forgot your password?
Sign in with
home
articles
Browse Topics
>
Latest Articles
Top Articles
Posting/Update Guidelines
Article Help Forum
Submit an article or tip
Import GitHub Project
Import your Blog
quick answers
Q&A
Ask a Question
View Unanswered Questions
View All Questions
View C# questions
View C++ questions
View Javascript questions
View Visual Basic questions
View .NET questions
discussions
forums
CodeProject.AI Server
All Message Boards...
Application Lifecycle
>
Running a Business
Sales / Marketing
Collaboration / Beta Testing
Work Issues
Design and Architecture
Artificial Intelligence
ASP.NET
JavaScript
Internet of Things
C / C++ / MFC
>
ATL / WTL / STL
Managed C++/CLI
C#
Free Tools
Objective-C and Swift
Database
Hardware & Devices
>
System Admin
Hosting and Servers
Java
Linux Programming
Python
.NET (Core and Framework)
Android
iOS
Mobile
WPF
Visual Basic
Web Development
Site Bugs / Suggestions
Spam and Abuse Watch
features
features
Competitions
News
The Insider Newsletter
The Daily Build Newsletter
Newsletter archive
Surveys
CodeProject Stuff
community
lounge
Who's Who
Most Valuable Professionals
The Lounge
The CodeProject Blog
Where I Am: Member Photos
The Insider News
The Weird & The Wonderful
help
?
What is 'CodeProject'?
General FAQ
Ask a Question
Bugs and Suggestions
Article Help Forum
About Us
Search within:
Articles
Quick Answers
Messages
Comments by TechJosh (Top 5 by date)
TechJosh
28-Jan-15 12:57pm
View
I just want to reiterate what everyone else here has said, you shouldn't be using per-row variables in a table trigger as you don't know that only one row will ever be deleted...
That said, the sql code itself is faulty as well. For instance, this is like a tiger chasing its own tail:
select @wineid = wineid
from instock
where wineid = @wineid
Since @wineid starts off null the where clause specifies that the instock.wineid must be null, which if the database is properly designed, is probably not possible.
It might be possible to write just a DELETE statement as your trigger that uses the deleted table in a subquery to find all StoreIds/wineIds that should be deleted. Perhaps something like this:
DELETE FROM bigstores bs
INNER JOIN deleted d
ON bs.StoreId = d.StoreId
CROSS APPLY (
SELECT SUM (Quantity) as Quantity
FROM instock (NOLOCK)
WHERE instock.StoreId = d.StoreId
) AS Calc
WHERE
Calc.Quantity < 50000
However, bear in mind that this query only handles DELETE statements. If someone were to UPDATE the instock table your trigger wouldn't fire as it is written. You would need to add UPDATE to the list of operations the trigger fires on. You would probably also want a similar trigger on INSERT.
I don't have a clear enough view of your database and use cases. But in this case it seems that you may not actually need the BigStores table anyways and perhaps it could be replaced with a view that only returned StoreIds which met the "bigStores" criteria. Then you wouldn't be caching data which could go stale on you, and you wouldn't need triggers to maintain the table.
CREATE VIEW bigstores
AS
SELECT
StoreId,
SUM(QUANTITY) AS TotalQuantity
FROM
InStock WITH (NOLOCK)
GROUP BY
StoreId
TechJosh
28-Jan-15 12:40pm
View
You also have 3 IDisposable objects which you should consider wrapping in a using() statement.
TechJosh
28-Jan-15 11:56am
View
Yeah, somehow my copy & paste munged <member> into an html tag.
TechJosh
28-Jan-15 11:54am
View
You are correct... I'll update my answer. Also I'd make the interface public or internal, private interfaces are of limited use and generally only when implementing child classes with a common interface.
TechJosh
28-Jan-15 11:51am
View
I don't see why that would be necessary. You're not trying to make a factory that can return instances of any type that matches the type parameter... your making a factory that returns a specific type. And your specific type is declared in the class declaration.
You could support multiple types in the same class if you wish, multiple specific types by implementing the IFactory<t> interface for each type. <pre lang="c#">public class MemberRoleFactory : IFactory<Member>, IFactory<Role> If you want to support all types (that match the IFactory<t> type constraints) you could make it generic but I don't think thats what is being asked here.