| Author |
Topic |
|
sth_Weird
Starting Member
38 Posts |
 Posted - 2009-04-21 : 10:21:24
|
| I'm new to this board and rather new to sql server triggers so...hi@all!I use SQLServer 2005.Straight to my problem...I manage a database with around 150 tables, the structure is quite complex. Therefore I wrote a management software. Users are supposed to use this software if they want to change data in the database. 99% of the users are fine with it, but some are a bit rebellious and I just want to be sure they don't change data in the database without the software.Therefore I want to create a trigger on some tables that fires if the table gets changed (no matter if inserted, updated or deleted) and another condition is true.What I'm looking for now is a way to check what kind of a change it was (insert, update or delete) and, in case of an update, I want to know if data has changed at all or if it remained the same (I mean you can perform an update command and write exactly the same data into the tables as there already is...I do not want to log that!), without checking every single column. Is that possible?That's what I need:CREATE TRIGGER Trigger_SecurityCheck ON [dbo].[tXYZ] FOR INSERT, UPDATE, DELETE ASBEGIN IF it was an update or delete create log entry EDIT: I found an example here how to do this, so that part is solved ELSE // it was an update IF any of the columns in this table changed data at all create log entry ENDIF ENDIFENDI think I can figure out the rest with checking the conditions and creating the log myself (at least it sounded rather easy when I googled for it), but I haven't found a nice example on how to solve the above, do have have to check every single column data in order to check if something changed at all, of is there something like a "if columnchanged" statement?)thank you!sth_Weird(sorry if there are faults in my vocabulary/grammar, I'm not a native speaker) |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-21 : 10:29:24
|
| Best solution is to lock down your database and encrypt the password on the application side. Triggers are bad for debugging and implementing them on 150 complex tables is very maintenance heavy.If an application is designed properly, it will contain an object model. In the object model, you can find out if an entity has changed and log the changes etc. |
 |
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2009-04-21 : 10:34:06
|
| that would be a good idea if it wasn't for the fact that the database uses the windows account as login, and I do not have enough admin rights to change this (I can create and alter tables and create roles and things like that, but I can't create new users for example).didn't see your edit yet when I answered...My application is devided into different "parts" there the graphical user interface, which does not know anything about the database, there are classes which represent the data in the database in an object orientated way, then there's a database interface library which queries the data and fills the data objects, or writes the data from the data objects back to the database. But there is no fix binding if that's what you mean, since I do not want to depend on the database to make the application work. It can also get the data from xml, for example. I also don't want to log the changes in all tables, just the two or three tables that might be target of the "attack".thank you for your answer!sth_Weird |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-21 : 10:36:53
|
| I think you should be able to allow only the "admin" account access to your database (database > security > users in SQL 2005). This will only fix the problem IF your application does not also use windows authentication with the SQL server which is really bad. |
|
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2009-04-21 : 10:45:38
|
| oi, seems like one of us is editing his old posts while the other posts an answer to the unedited one *lol*your idea is actually quite good and came to my mind immediately. But (yeah I'm afraid there's always a but) I'm not the only one who wrote an application for the database. There are other users writing applications, each of them covers another "part" of the database. If I wanted to change the access, they all had to do so, too. I'm not sure if they'd be too happy about this idea, plus, the possible rebells might find out I'm trying to "spy" on them and I do not want, because I do not want them to think I don't trust them, actually I trust them 99% but I just want to make sure of the 1% that is uncertain. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-21 : 10:54:15
|
| Have a look at thishttp://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.htmlIt logs chages to data to a single table.There are other examples here of doing similar thingshttp://www.nigelrivett.net/#TriggersYou can easily generate a trigegr that will check each column for changes using the PK.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2009-04-22 : 05:03:04
|
| thank you for your reply.I don't understand much about what the trigger does, but it works fine now!:-)sth_Weird |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-04-22 : 10:51:53
|
quote:
Originally posted by whitefang
This will only fix the problem IF your application does not also use windows authentication with the SQL server which is really bad.
Windows Authentication is bad?Good Lord, Whitefang....________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
Trigger to list table changes