14 replies to this topic

[shivam]

Is there any way to create a relationship between the columns of a same table in sql server.:c-penguin:.

Let suppose i have 2 Columns in a table named Issued_date and return_data Now i want that if i assign the value of Issued_date the Return_date automatically entered which is Issued_date + 15 .:c-cool:

I want all this at the database level for integrity not at programming level:cool:..

Anyone..!!!:c-smile:

[LuthfiHakim]

This is the job for triggers, if your database supports. Triggers are set of commands that automatically executed when insert, update, or delete operations happen on the table (or view). Check your database documentation to see does it support trigger or not.

[WingedPanther]

SQL Server does support triggers. It sounds like a bad idea to do it this way, however. If your business logic changes, you'll have to rewrite the trigger, which is a lot less convenient than changing your software. Your software may not have the necessary privileges to modify a trigger for your users as part of an upgrade.

[LuthfiHakim]

^ Lol, sorry I did not catch that he did mention sql server. Could you elaborate better idea if you think using trigger is a bad one for this case.

[WingedPanther]

I would have it be part of the code. If it truly is a static difference, he could even avoid the column entirely (for now) and do something like:

SELECT Issued_date, Issued_date+15 as Returned_date FROM table;

That way, he gets the same effect without using the database space. Either way, simply ensuring Returned_date is always populated isn't a huge burden. If it's a mandatory field, setting it to NOT NULL will catch errors, or a trigger could abort the insert/update statements if minimal business logic isn't met by the statement (Issued_date < Returned_date, can't have one null but the other not, etc).

I just know from experience that when things change, it can be very unpleasant have to rework a database structure. Having triggers in place can just make that process even harder.

[LuthfiHakim]

Quote

SELECT Issued_date, Issued_date+15 as Returned_date FROM table;

While indeed that is a nice trick, but I believe it also a common trick. I mean OP must have known this but ask for different approach.

Since your idea advocates doing the "integrity" operation through application level, don't you think it is even more troublesome hunting sql codes cluttered inside the application whenever business rules changes happened? Compare that effort with the effort just to fix a trigger.

[WingedPanther]

Unfortunately, business logic has a nasty habit of changing in weird ways.

Rev 1: Returned_date is Issue_date+15
Rev 2: Returned_date is Issue_date+15, unless customer='ABC', in which case Returned_date is Issue_date + 30
Rev 3: Returned_date is Issue_date+15 if priority = 'normal' and customer <> 'ABC', Returned_date is Issue_date+5 if priority = 'high' and customer <> 'ABC', Returned_date is Issue_date+30 if priority = 'normal' and customer='ABC', Returned_date is Issue_date+20 if priority='high' and customer='ABC'
Rev 4: now we add a new special customer...

If this is meant to be generic software to be sold to multiple different companies/facilities, things get even messier, as all this logic gets further splintered.

If this is an application for a small company/application that expects minimal growth, then it probably doesn't matter much. I've dealt with customers that had multiple facilities, where they all used the same software, but separate backend databases and completely independent business logic. When they finally realize they need to merge into a single database, having all that logic embedded at the database level would have been a nightmare. Having it at the application level (especially in configuration files that are separate from the application), made things a lot easier.

I've also seen what happens when you have to completely restructure tables to accommodate increased flexibility. That can get really nightmarish.

[LuthfiHakim]

Why am I under the impression that you think triggers can not look into other tables or the values entered/updated/deleted? All of those revisions samples could easily be solved with a helper table containing the rules. Simply add rules to this table and let the triggers look which rule it should use. Or did you write unique application for each customer that for that tiny difference?

For the last problem example, in ms sql server the trigger could inspect the session to see what application invoke it, if you want the trigger to act differently based on the application.

[WingedPanther]

I do know about that. Maybe it's just a matter of perspective.

[sam_l]

And WingedPanther is correct. The database should not be cluttered with things to take care of something that you can easily take care of at a higher level.

[LuthfiHakim]

^ Then you don't know what we are talking about. We are talking about upholding the integrity of the database. Not talking about any code unrelated with keeping the integrity. Or do you think people that providing triggers in their database are bunch of fools?

[sam_l]

Quote

^ Then you don't know what we are talking about. We are talking about upholding the integrity of the database. Not talking about any code unrelated with keeping the integrity. Or do you think people that providing triggers in their database are bunch of fools?

I think the code the generates the insert query is related to keeping data integrity, but I must be wrong.

Quote

Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database.

A trigger is activated by a change in the database, so it goes AGAINST data integrity as I read it. WingedPanther already mentioned why triggers are not the way to go for this at all.

"WingedPanther" said:

SQL Server does support triggers. It sounds like a bad idea to do it this way, however. If your business logic changes, you'll have to rewrite the trigger, which is a lot less convenient than changing your software. Your software may not have the necessary privileges to modify a trigger for your users as part of an upgrade.

The reasons he provides are excellent and should be headed for this case.