| Author |
Topic |
|
dbwilson4
Yak Posting Veteran
50 Posts |
 Posted - 2007-07-03 : 13:14:42
|
| How do I create a trigger in MS SQL 2005 studio mangement? (would like to use a GUI interface to create if possible) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-03 : 13:24:02
|
| You would use the CREATE TRIGGER syntax. Check SQL Server Books Online (the documentation for SQL Server) for the syntax and examples. If you have a more specific question, then please tell us what you want it to do and show us what you have already tried.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-03 : 13:32:54
|
| Ok, I just looked up a tutorial and successfully created one. However, where does it show up? I created it on the correct database but it doesn't show up under "Programmibility > Database Triggers". I tried to create it again but it tells me that it exists??Also, the trigger uses a enumerated datatype that in the Application is equal to 2. How do I define this enumerated datatype in sql? So if I change in the application, I only have to change it in one place in SQL database. (For example, application has enum datatype Status { Active = 0, Deleted = 2, Pending = 3 } etc) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-03 : 13:41:25
|
| It would show up under Triggers when you expand the table. They are not database triggers, but rather table triggers.For the enumeration table thing, you could store this in a table.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-03 : 14:06:17
|
| Cool thanks but for the enumeration, I cannot store it in a table because alot of application logic is also done in the application layer. Therefore, there needs to be a enumerated datatype of Status in the application layer. Is it possible to copy that enumerated datatype to sql server so I can use it in the trigger? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-03 : 14:11:32
|
| Triggers have access to everything that is inside SQL Server and also the data that is being inserted/updated/deleted. So if you can get the enumeration into the data or somewhere else in SQL Server, then the trigger will have access to it.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-03 : 14:14:28
|
| If the datatype is in the table and associated with the record (that appears to be the case) then it is data not code. I would seriously consider storing this in a table, with a foreign key constraint, and have the application look it up from there. This would satisfy the "in one place" requirement.Enumerations are valuable in the application layer but if they are actually attribute values for data entities rather than application utilities then what you are aiming for sounds a little like the tail wagging the dog. In the same manner, you wouldn't store a class definition in your database.My $0.2 |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-03 : 14:33:12
|
| Basically, all my application objects map to a database table (album class maps to album table, blog class maps to blog table). Each of these objects have a GENERIC status column (active, pending, processing, deleted, archived, sent, updated, deleted). All my queries filter out the deleted items (select * from album WHERE NOT status = " + Status.Deleted).I think storing this status column as a database table...is kind of ridiculous. Advanced buisiness logic would be complicated with a normalization like that. Also, with having it as a table, I would have to create columns like IsDeleted, IsActive, IsPending, IsProcessing in the status table. Am I wrong or is that the preferred way to do things? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-03 : 14:37:48
|
| I don't see why you would need all of those extra columns in your table. Don't people just use a bit column to indicate which one(s) is(are) true?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-03 : 14:48:12
|
quote:
Originally posted by dbwilson4
I think storing this status column as a database table...is kind of ridiculous.
Actually it is more the other way round. You have stored the meaning of data that appears in your database in your application. Your data has no meaning unless your application is plugged into it. As such, your database is inextricably coupled to your application.A linky:http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspxquote:
Originally posted by dbwilson4
Advanced buisiness logic would be complicated with a normalization like that. Also, with having it as a table, I would have to create columns like IsDeleted, IsActive, IsPending, IsProcessing in the status table. Am I wrong or is that the preferred way to do things?
I would say this is elementary data logic (there is a difference). You would not change the format of a single one of your tables except to add a foreign key. You would definately not want to start adding IsDeleted columns all over the place.quote:
Originally posted by dbwilson4
All my queries filter out the deleted items (select * from album WHERE NOT status = " + Status.Deleted).
You would still be able to do this and your application would stay pretty well the same. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-03 : 15:02:02
|
In case what I am suggesting is not obvious, this is all I mean:CREATE TABLE RecordStatuses ( StatusId TINYINT , StatusDesc VARCHAR(20) NOT NULL , CONSTRAINT pk_RecordStatuses PRIMARY KEY CLUSTERED (StatusID) , CONSTRAINT ix_RecordStatuses_StatusDesc_nc_u UNIQUE (StatusDesc) )INSERT INTO RecordStatuses (StatusId, StatusDesc)SELECT 0, 'Active'UNION ALLSELECT 1, 'Deleted'UNION ALLSELECT 2, 'Pending--... and so on Exactly the same format as your enumerations. Your other tables do not change. You don't even have to set up foreign keys for referential integrity but I believe you should.Your application gets the status codes from the database table and uses them in place of the enumerations. Better still would be to use stored procedures rather than direct table access but I won't push my luck  |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-03 : 15:16:55
|
| I come from having to maintain about 600 SPs all starting with the name msp_ in one of the projects I worked on. I really think these industry standards need to be reexamined. I'm write page long select queries with complex counts (all dynamically generated sql), all of them run under a second with over 10 million data (on a medium server).The status column is purely used for buisiness logic and while it relates to the data as in "place an identification on it", it is still governed by buisiness logic which should never be in the database ihmo. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-03 : 15:34:42
|
| Deleted is business logic? Pending is business logic? It is data.My job title was assigned to me when I joined my company but it is not business logic - it is data about me. The rules for determining the status of your rows (and my job title) may be business logic but the final result is data.EDIT (Oh - I hate myself....)If it is not data why does your database need to know about it?I think the sprocs debate is best left for another time. Anyhoo - you know my opinion on this. I doubt I can say much more without annoying you so I'll leave you to it. I suspect that 90% of the people on this board will agree with me and perhaps they will chime in with something more persuasive (or figure out how to store your data in the application and make it available to the database) and help you out with your problem. |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-03 : 19:19:39
|
| LOL, I guess it is ultimately "data", you got a point there heh but what you would be doing is normalization FOR SOMETHING so simple and has no meaning other than data identification in the application layer. If everything was 100%, performance is out of the question. I don't see a reason why status should be in a lookup table, for what it does in the application logic at least. I guess different strokes for different folks (btw, you are not annoying me, I like these types of debates). Thanks for info. |
|
|
|
|
How to create trigger?