Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had the following quiz question and could not answer it myself because of a little confusion.

Please answer this and tell me why?

A database trigger is an alternative for?

A. Stored procedure
B. Primary key for implementing referential integrity
C. Foreign key for implementing referential integrity
D. All of the above

share|improve this question

migrated from stackoverflow.com Feb 27 '13 at 18:24

This question came from our site for professional and enthusiast programmers.

1  
If you go into development as a career, I would also suggest avoiding triggers if at all possible. When you start designing applications triggers sound great! You can do so much in a trigger. But then you eventually realize it makes code so much harder to debug because logic is being executed at the DML level out of view of your application. Eventually you realize triggers just mask bad development practices. Put your logic in the code, not a trigger. –  Wolf Feb 27 '13 at 20:44
3  
@Wolf Bad advice. Triggers are brilliant in many different situations. –  Phil Feb 27 '13 at 20:55
3  
Cheers to Phil for standing up against the church of anti-triggers. All the tools have their place... even cursors! :-O As others have explained none of the choices are really very good, but it's likely they are looking for D based on the context. –  Eric Higgins Feb 27 '13 at 21:14
4  
@Wolf: As much as I don't like triggers, I don't understand the last part of your comment. Triggers are code, too. Unless your advice is to put nothing in the database, no triggers, no stored procedures, no Update/Merge/Insert statements, no Create Tables ... –  ypercube Feb 27 '13 at 21:21
2  
Well these are interesting responses. I did say "avoid ... if possible" not never use. Triggers can be useful for assigning a sequence to a PK column in Oracle, or more complex constraints and data validations. But when triggers start to implement application logic, such as firing off subsequent business processes like invoicing when really your application should handle that, that's what I don't care for. You then have bits of logic stuck away in corners firing off without your explicit instructions and then making changes and debugging becomes so much harder. –  Wolf Feb 27 '13 at 22:52
show 1 more comment

4 Answers 4

I don't expect the writer of the question meant it this way, but words mean things and the question specifies a "database trigger", therefore there is no correct answer.

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event. (PL/SQL Language Reference - DATABASE Triggers)

Unlike Row and Statement triggers, Database triggers fire for things like AFTER STARTUP ON DATABASE, AFTER SERVERERROR ON DATABASE and AFTER LOGON ON DATABASE.

share|improve this answer
6  
+1 for reading carefully! –  Jack Douglas Feb 27 '13 at 21:16
add comment

None of the above.

It would be exceptionally hard to do correctly in a multi-user system (and it would be radically less efficient than doing it correctly) but it is theoretically possible to implement a pseudo-primary or foreign key using a set of triggers. Roughly no one that tries would actually implement it correctly but it could be done.

There is some logic that you might implement in a stored procedure that could be implemented in a trigger. But a trigger is hardly a replacement for a stored procedure. You can't call a trigger from an application or pass in parameters, for example, so it is hardly worthy of being called a replacement for a stored procedure. And there are a ton of things that a stored procedure can do relatively easily that are radically more difficult in a trigger or that will transform your system into a spaghetti-fied mess of triggers trying to work around mutating table exceptions.

share|improve this answer
add comment

I think the answer is actually supposed to be C although the question sucks. Now I will justify both parts of that statement.

When I started using PostgreSQL, you could only do C through triggers and check constraints calling UDF's. Many of the database books I read recommended using triggers for referential integrity. It's nice that we have moved on but there are times and places where the limits of declarative referential integrity get in the way and one still ends up having to code constraint triggers by hand. Examples may include foreign keys based on derived information (internet address is in a stored CIDR block) or other cases where typical foreign key mechanisms are inadequate. The legacy of using triggers to manage referential integrity is thus not dead at all.

The question however is entirely inadequate because nobody in their right mind would hand-code procedural triggers where declarative referential integrity will work. You can do it. That doesn't mean it is a good substitute.

On some databases, like PostgreSQL, triggers require stored procedures and are therefore not a substitute for them. Similarly while I suppose it is theoretically possible to do something like a primary key using a trigger, typically this is done using an index instead.

Also I am going to disagree with Leigh above. Just because a trigger can be used to do a lot more than FKey checking doesn't mean that it isn't a historical and even current method to do this. It can be far broader but the use cases of triggers do include referential integrity enforcement and so a close, careful reading of the question suggests the answer is C and this is not contradicted by anything Leigh says above ;-).

share|improve this answer
    
I think the answer shouldn't be C, as it's very difficult to get right and working at all transaction isolation levels. –  Colin 't Hart Feb 28 '13 at 9:39
    
@Colin'tHart So how would you manage foreign keys where declarative foreign key semantics is inadequate, for example "IP address in table A is in a CIDR block in table B?" –  Chris Travers Feb 28 '13 at 9:42
    
You want me suggest using a trigger to perform the "in" query, but it will allow IP address inserts in A in the case when a concurrent transaction is deleting the referenced CIDR block in B when in "repeatable reads" or "read committed" transaction isolation level. This can be solved using extra locking, or if the app uses "serializable" (or "read uncommitted"...) transaction isolation level. –  Colin 't Hart Feb 28 '13 at 10:00
    
I think you can do this declaratively in certain DBMSes, eg PostgreSQL using exclusion constraints, or in Oracle using a product such as Rulegen (which manages the creation of the complex trigger code for you). –  Colin 't Hart Feb 28 '13 at 10:02
    
Exclusion constraints in PostgreSQL are very different (and not supported for built-in network types). We can (and do) use ip4r with exclusion constraints for something similar but that's a guarantee on the primary key side of the RI issue, not the foreign key side. Basically an exclusion constraint lets you guarantee that you don't have overlapping CIDR blocks in your CIDR table. It offers nothing on the fkey side. –  Chris Travers Feb 28 '13 at 10:08
show 5 more comments

I'd go with D. triggers can be used for A-C

As to why... well

For A: Managing inventory can be a complex process. using triggers allows for flow of information based on data being keyed into a table; so that if someone bypasses the UI the triggers prevent bad/erroneous data even if someone trys to "Script the solution"

Using triggers for PK/FK relationships allows for cascade update/deletes. The triggers could be put in place ot prevent orphaned records as opposed to using actual PK/FK constraints. As to why you may choose this model; it would allow for changing of keys and possibly handle cascade updates/deletes. But then, that's really the intent of PK/FK and cascade functinoality.

share|improve this answer
2  
The end of the answer appears to be missing –  user18565 Feb 27 '13 at 18:46
    
Please explain how you can use a trigger as an alternative for a primary key? –  Colin 't Hart Feb 28 '13 at 9:35
1  
While you can use a trigger as an alternative for a foreign key, it's very difficult to get completely correct and requires lots of locking to make it work at all transaction isolation levels. So practically speaking, you can't use triggers as an alternative for foreign keys. –  Colin 't Hart Feb 28 '13 at 9:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.