I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -
LoginName varchar(100) - user name
Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'
TableName varchar(30) - name of the table updated
PrimaryKey int - primary key of the record updated
ColumnName varchar(30) - name of the column updated
OldValue varchar(1000) - old value converted to varchar
NewValue varchar(1000) - new value converted to varchar
RecUpdDate datetime - record update date.
This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance? 2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
I have two tables (categories & listings) which create a many-to-many relationship.
I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).
How does the interim table get populated with the id's?
When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.
I have been reading a bit about db design practices for SQL server and have learned how bad composite keys are!
However, I just wanted to know what the accepted alternatives are (and why they should be used);
Previously I was under the impression that adding autonumber style keys to tables are bad (extra col, doesn€™t allow unique checking of tuples). I want a table which is made up of two (single) primary keys from other tables, but the entries in the new table to be unique (and efficient). (note this is going to be part of a db which is going to grow to become very large).
Could somebody please explain to me how to create a foreign key from a table that has two composite keys? I have a table, UserPrecedence, with two composite keys - up_owner, owner_userID. I have a second table, Users, that has the primary key "emailAddress" and a userID table to which all other tables point their FK columns.
I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence but the Modify Table view complains that "both sides of the relationship must have the same number of columns" when I try to create the relationship.
I am guessing this is because its a composite primary key. Can somebody explain to me how this is done correctly (and why)?
Hi, i want to make a reference from a table on itself. The table has a composite Primary Key. But I just want to refernce the TEstCaseID. So whats wrong? Can anyone help me?
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. Â This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? Â While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools] FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId) Or CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
Dear Group,I would like to create an audit table that is created with a trigger thatreflects all the changes(insert, update and delete) that occur in table.Say I have a table withSubject_ID, visit_number, dob, weight, height, User_name, inputdateThe audit table would have .Subject_ID, visit_number, dob, weight, height, User_name, inputdate,edit_action, edit_reason.Where the edit_action would be insert, update, delete; the edit_reason wouldbe the reason given for the edit.Help with this would be great, since I am new to the world of triggers.Thanks,Jeff
I have a windows 2012 server and will like to know how to audit DMLs on a table (delete, truncate, update) on this table, I want to see all T-Sql DML statement carried out on this table in a file.How can this be achieved using if possible something already built into SSMS.
I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,
I am having 3 tables, table1 table2 table3
User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).
I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,
TableA
CountryId CountryCode CountryName 1 IND INDIA Update TableA Set CountryCode = 'INDI', CountryName = 'INDIA' Where CountryId = 1
On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.
i'm in a bit of a bind at work. if anyone could help, i'd greatlyappreciate it.i have a web app connecting to a sql server using sql serverauthentication. let's say, for example, my login/password isdbUser/dbUser. the web app however, is using windows authentication.so if I am logged into the network as 'DOMAINEric', when I access myweb app, my web app knows that I am 'DOMAINEric'. but to the sqlserver db, I am user 'dbUser'.now, i for each table i have, i need to implement an audit table torecord all updates, inserts, deletes that occur against it. i wasgoing to do so with triggers. this is all fine for selects, inserts,and updates. for each table, i have an updatedby and an updatedate.for example, let's say i have a table:create table blah(id int,col1 varchar(10),updatedby varchar(30),updatedate datetime)and corresponding audit table:create audit_blah(id int,blah_id int,blah_col1 varchar(10),blah_updatedby varchar(1),blah_updatedate datetime)for update and insert triggers, i can know what to insert into theupdatedby column of audit_blah because it's in a corresponding row inblah. my web app knows what user is accessing the application, andcan insert that name into blah. blah's trigger will then insert thatname into audit_blah.however, in the case of a delete, i'm not passing in an 'updatedby',because i'm deleting. in this situation, how can the trigger knowwhat user is deleting? the db only knows that sql user 'dbUser' isdeleting, but doesn't know that 'dbUser' is deleting on behalf of'DOMAINEric'. is there any way for my app to inform the trigger toaccess my windows identity without having a corresponding row in thetable from which to pull that info?obviously, i could have each of my app's users log into SQL serverthrough Windows authentication; then i could just use SYSTEM_USER.but let's say, for performance's sake, it'd be better for me to useone sql server login. (i believe one user works better for connectionpooling purposes.) is there a way to get around this?(i'm hoping a built-in function exists that solves all my problems.)suggestions? resources?any help would be great appreciated.happy turkeys.Eric
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
hi. How to update FormA table from customer table. Let say i wish to keep small number of fields from each table so i use foreign keys as reference. However i had a problem when i tried to save the relationships of both tables, i receive the error that FormA_id is not able to insert null into value. Cust_id(PK) is identify column, as well FormA_id(FK) and FormA_id(PK) too. For example, when i insert a record from customer table, it will automatically create id for FormA. Table structure. Customer cust_id(PK),name,age,formA_id(FK) Table structure, FormA formA_id(PK), info, date, How to solve ?
I have a setup with a bridge table. There are about 5 different tables on one side of the bridge (all with compatable PK columns) one of which is called 'mobilesub', and one on the other side called 'allcostcenters'. The bridge table is called 'subaccountcostcenter'.
I can enter data for mobilesub in the bridge table. But then when I try to enter the info into the bridge table for any of the other tables, such as localsub, there is a conflict like this:
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_subaccountcostcenter_mobilesub'. The conflict occurred in database 'test1', table 'mobilesub'. The statement has been terminated.
Is there some rule against using a bridge table that references several different tables, and I'm just not aware of it. Because I've done everything I can to make sure the info from the different tables don't conflict . . . The same error comes up if I do the localsub table first--in that case the foriegn key messing me up is FK_subaccountcostcenter_localsub. So it's not something with the individual tables.
Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.
I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad.
Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)
Hello,We imported a bunch of tables from a database and realized that theprimary keys weren't copied to the destination db. In order to re-create the keys, we need to know which tables have them. Is there acommand that I can use (on the source db) to find out which tablescontain primary keys? The db has hundreds of tables and I'd rather notgo through each one to see which has a primary key.Also, for future reference, is there a way to include the primary keyon an import?Thanks,Peps
I've created a table called Employees with a primary key called EmployeeID. Â The table contains EmployeeID, FirstName and LastName columns. Â I now want to create a table called Team which will contain the columns TeamID, EmployeeID (to reference the column EmployeeID from the Employee table) and a column called TeamName. Â Sql won't let me create multiple primary keys in one table (I did think that was the case ) key but yet if I look at the Adventure Works database in the Person.PersonPhone table, I can see three primary keys defined.
I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.
I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.
If anyone has any insight to this, it would be greatly appreciated. Thanks,
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys ON Table_Borrower AFTER INSERT AS begin declare @bid as int
@bid = select MAX(BorrowerID) FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID) values (@bid) end GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys ON Table_Borrower AFTER INSERT AS
DECLARE cLoop cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@vcDB and TABLE_SCHEMA=@vcSchema order by TABLE_NAME ASC
open cLoop
FETCH NEXT FROM cLoop INTO @vcTable WHILE @@FETCH_STATUS=0 BEGIN if not exists (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @vcSchema AND TABLE_NAME = @vcTable AND CONSTRAINT_TYPE = 'PRIMARY KEY') print @vcTable + ' does not have a primary key'
FETCH NEXT FROM cLoop INTO @vcTable END Close cLoop DEALLOCATE cLoop
I have run into a problem, i need to find out that column(s) in a table that makes the primary key. I thought that this code did the trick. *** DECLARE @c varchar(4000), @t varchar(128) SET @c = '' SET @t='contact_pmc_contact_relations' Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid SELECT Substring(@c, 1, Datalength(@c) - 1) ***
This works in most of my cases. But i have encounterd tabels where this code doesn't work. Here is a dump from one of the tabels where it doesn't work. SELECT * FROM sysindexkeys WHERE (id = 933578364) <--id of the table *** id indid colid keyno 933578364 1 1 1 933578364 1 2 2 933578364 2 1 1 933578364 3 2 1 933578364 4 3 1 933578364 5 4 1 933578364 6 5 1 933578364 7 6 1 933578364 8 7 1
Not sure if that dump made any sense, but i hope it did. If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties
Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9} Similarly:: Buyer_Id :- BYXXXXXX Seller_Id :- SLXXXXXX
We are implementing the database in MS-SQL 2005 & MySQL
Can anyone help me find a solution to this, especially in MS-SQL.
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
Hello , i have 2 seperate tables of information about people. Table A :with a key column Anumber contains mobile telephone numbers and table B : with a key column named Bnumber contains mobile telephone numbers
These two key columns have the same data type and hold the same information (mobile phone numbers).
Some mobile numbers from table A exist in table B so i wanted to run a clustering algorithm in order to gain information from the two tables.
I created a new table C with all the distinct MobileNumbers found in the tables A and B ,set the Cnumber column as key columns and linked it with the equivalent columns Anumber , Bnumbers from tables A and B.
Although, when i desing a training model in the Business intelligent Studio ( New mining structure) and set table C as case table and A and B as nested tables in the "DataMiningWizard>Specify the columns used in your analysis" window the key columns from table B and C DO NOT appear at all So if i click next i get a warning (You have not defined a key column for the nested tables). I proceed, put the key columns manually from the mining structure tab (drag and drop the key columns from the data sourve view) but when i run the clustering algorithm the results doesnt at all make sence as you can see at
