September 19, 2011 at 10:59 am
Can after update triggers be used from executing a trigger in a table based on a different trigger
thanks
September 19, 2011 at 11:40 am
I'm not clear on what you're asking.
If you need to know if a trigger on Table A can do things (insert/update/delete) in Table B, then the answer is yes. And if Table B has one or more triggers on it, and the option for cascading triggers is enabled, then those can be fired by those modifications in Table B.
Is that what you're asking?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 12:21 pm
Sorry for the confusion
This is what i am trying to do
whenever a product or products are made inactive on the company table i want to make that company inactive fro which we no longer carry products
product table is defined as
productID smallint
ProductName char(3000)
Isactive bit
company table is defined as
productName char(3000)
CompanyName char(3000)
IsActive bit
so when a productName is discontinued we and isactive = false on product table the companyName should be made deactive as well as well, i hope that explained it
thanks
September 19, 2011 at 12:24 pm
Check the Recursive Triggers Enabled option on your database.
http://msdn.microsoft.com/en-us/library/ms190739.aspx
You should be able to trigger deactivating the company on condition that all products are deactivated.
September 20, 2011 at 6:24 am
SQLTestUser (9/19/2011)
Sorry for the confusionThis is what i am trying to do
whenever a product or products are made inactive on the company table i want to make that company inactive fro which we no longer carry products
product table is defined as
productID smallint
ProductName char(3000)
Isactive bit
company table is defined as
productName char(3000)
CompanyName char(3000)
IsActive bit
so when a productName is discontinued we and isactive = false on product table the companyName should be made deactive as well as well, i hope that explained it
thanks
Okay. That's not a trigger recursion issue. You just need a trigger on the Product table that updates the Company table.
Might look like:
create trigger CompanyInactive on dbo.Product
for Update
as
update dbo.Company
set IsActive = 0
where ProductName in (select ProductName from inserted where IsActive = 0);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 23, 2011 at 7:33 am
Joe, "...and companies all have a DUNS...." is incorrect. There is no requirement that a company have a DUNS, and, since there is a 30-day wait while they are issued, unless requrested by phone (which has fees attached to it), even companies that plan to get one won't necessarily have one at all times. Many individuals won't have one, but would qualify as "companies" in this context if they can provide a product that needs to be tracked in this database.
The rest of what you posted is just useless. That piece is just plain wrong.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply