How To: Create A Table That Only Allows Inserts And Deletes.
Feb 26, 2008
I need to create a table that only allows records to be inserted or deleted. Once the record has been created it can only be deleted. Is there anyway to configure a table in this manner?
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_PCL_LENS_DATA](
[SerialNumber] [varchar](50) NOT NULL,
[ProcessedDate] [datetime] NOT NULL,
[Filename] [varchar](50) NOT NULL,
[CartonLabelImage] [image] NOT NULL,
[ExpirationDateLabelImage] [image] NOT NULL,
[LabelSetLabelImage] [image] NOT NULL,
[ReplyCardLabelImage] [image] NOT NULL,
[TextFile] [ntext] NOT NULL,
CONSTRAINT [PK_TBL_PCL_LENS_DATA] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Please help.I have a table with single row. I need to allow only UPDATEs of thetable, forbid INSERTs and DELETEs. How to achieve it?Thank you for information/RAM/
SQL 2K - SP3a - Development BoxThe environment is a piece of garbage ... triggers firing triggers firing triggers. Application is a piece of garbage that a developer want to make better (yea!) I need to put together a profile template to capture inserts and deletes into two tables. Not asking for the fish (but will accept it if necessary). Looking thru BOL, cannot find find an event class to capture insert and / or delete. Looking at TSQL event class gives SQLStmtCompleted event, and maybe I can filter on the two object IDs (object name not available because dbname not available as data column for this event.) Or if exists, but not documented, the event class for insert and delete. Any ideas?
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
I am trying to create a stored procedure that Deletes Table if there is Data.
Also stored procedure will Insert new data into table.
I have already created table. This is part of my current stored procedure.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMACjmerlos].FHLMC_Trials') AND type in (N'U')) DELETE srd.[PNMACjmerlos].FHLMC_Trials
I suspect I have a but in a stored procedure that is used to delete records in a table. Here's the code for the gridview and its SQLDataSource 1 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 2 DataKeyNames="table2_id" DataSourceID="SqlDataSource1"> 3 <Columns> 4 <asp:CommandField ShowDeleteButton="True" ShowSelectButton="True" /> 5 <asp:BoundField DataField="table2_id" HeaderText="table2_id" 6 InsertVisible="False" ReadOnly="True" SortExpression="table2_id" /> 7 <asp:BoundField DataField="table1_id" HeaderText="table1_id" 8 SortExpression="table1_id" /> 9 <asp:BoundField DataField="name_2" HeaderText="name_2" 10 SortExpression="name_2" /> 11 <asp:CheckBoxField DataField="yesno" HeaderText="yesno" 12 SortExpression="yesno" /> 13 <asp:BoundField DataField="MyArtificialColumn" HeaderText="MyArtificialColumn" 14 ReadOnly="True" SortExpression="MyArtificialColumn" /> 15 </Columns> 16 </asp:GridView> 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 18 ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" 19 DeleteCommand="EraseIt" DeleteCommandType="StoredProcedure" 20 SelectCommand="StoredProcedure5" SelectCommandType="StoredProcedure"> 21 </asp:SqlDataSource> Recently, "Wreck of u" kindly provided me with the stored procedure "StoredProcedure5" (line 20) which populates the gridview as shown. What I would like to now is write a stored procedure that DELETES a row. As like 19 shows, I attempted to write such an SP called EraseIt. However, when I click on Delete in the browser for a row, I get the error message "Procedure EraseIt has no parameters and arguments were supplied. The following is the code for EraseIt. Do I in fact have an error with specifying the input parameter "@table2_id", and or am I doing something else wrong? 1 ALTER PROCEDURE [dbo].[EraseIt] 2 AS 3 DECLARE @TABLE2_ID int 4 BEGIN 5 DELETE FROM DBO.TABLE2 WHERE TABLE2_ID = @TABLE2_ID 6 END 7 Thanks!-Kurt
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.
An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.
If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.
I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?
will my fetches on a cursor continue seamlessly even if deletes and multiple commits are occuring on the underlying source table? I want to make sure the cursor will not lose it's place as I believe some older dbms's did.
Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables. However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server). Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
Hello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts.
Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement:
INSERT INTO FieldOps(StudySiteID , QA_StructureID , Notes , PersonID) SELECT DISTINCT StudySiteKey , QA_StructureKey , SampleComments1 , '25' FROM ScriptOutput_Nitrate WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL)
and SQL Management Studio (eventually) says: (463 row(s) affected) (463 row(s) affected)
(25 row(s) affected)
The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference:
USE [master] GO ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
use FieldData GO DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS GO
USE [master] GO ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO
I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.
Dear All, I developed an application years ago where I have to insert records in multiple table from asp.net page during registration process with one button click. I have seperate Insert statements and then i have to select Identity column value and insert into another table as a foriegn key and so on. I want to run all these SQL insert in one statement or Stored procedure. becuase currently, if one of insert statemetn fails, I have no way to roll back previous inserts and start over again.
Any advice or sample T-SQL Where I can insert in Table A, and then read the identity from Table A and insert in Table B, in one single Transaction, so if it fails at end of Transaction, i want to roll back the all the inserts and updates in table(s).
I've been looking online and cannot find any help / resources with this so I brought it here :D I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables. Any resources / help would be great! I work best by example.
I have data in an old database I would like to capture for my new system but I dont have the original insert scripts. Is there a tool (in SQL Server 2000 or thirdparty) that will help me export the data as SQL inserts?
I am working on an application to analyse down time on a production line system. The system has about 40 rows inserted per minute. The inserts are coming from about 10 different stations.
I need to a analyse the downtime between each insert from each station. The plan is to copy the data to another database on a different server so as not to affect the live system that is being updated by the production line.
However the initial requirement was to do this at night while the production line was down but now they want the data to be updated every 3 hours which means performing this huge query while the production line is bombarding the DB with inserts.
I am wondering what is the best way of doing this. Is there any way I can limit the abount of processor this proceedure will take.
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
I have a situation here. Please advice me on this.
I have a master table and a child table. They have a PK and FK relationship. The master table has an identity column with auto increment set to true. This map as a FK in the child table. My questions are:
Can I have a single form to insert a new record into master and child table at the same time?This has to be accomplished without stored procedures. Can it be done?Is it possible to do this with a single insert query? If yes, can it be done with sql data source or dataset or tableadapters’?Please point me towards appropriate link for doing so.
I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this. this is my table info table = BusinessInfo Columns = BusinessID, BusinessName, BusinessAddress how can i create a stored procedure ?
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.
Code Snippet DECLARE @ImportedStagingData TABLE ( ID INT IDENTITY(1,1), Name VARCHAR(50), Surname VARCHAR(50), Email VARCHAR(50), [Chronic Heart Failure] INT, [Colon Cancer] INT ) INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0) INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1) INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1) INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1) INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0) DECLARE @Conditions TABLE ( ID INT IDENTITY(1,1), Condition VARCHAR(50) ) INSERT INTO @Conditions VALUES ('Chronic Heart Failure') INSERT INTO @Conditions VALUES ('Colon Cancer') DECLARE @Members TABLE ( MemberID INT IDENTITY(1,1), Name VARCHAR(50), Surname VARCHAR(50), Email VARCHAR(50) ) DECLARE @memConditions TABLE ( MemberID INT, ConditionID INT ) SELECT * FROM @ImportedStagingData SELECT * FROM @Conditions SELECT * FROM @Members SELECT * FROM @memConditions /* --- This is the part that i am battling with --- DECLARE @CurrentValue INT DECLARE @numValues INT SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData) WHILE @numValues <> 0 BEGIN INSERT INTO @Members SELECT Name, surname, email FROM @ImportedStagingData GO SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData')) INSERT INTO @memConditions (MemberID), (ConditionID) VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL Drop table Bezeichnungen; GO create table Bezeichnungen ( Artikelnummer nvarchar(18), Artikelbezeichnung nvarchar(80), Artikelgruppe nvarchar(13),
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
CREATE TABLE [Sales].[Test_inmem] ( [c1] [int] NOT NULL, [c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),
[Code] ....
I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.
Assuming I should be using values from temp inserted to insure correct record... Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form AFTER INSERT
AS SET NOCOUNT ON -- Checkbox Driven: IF a_form.missingCheckbox = -1 THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven: IF a_form.incorrectTxtbox <> 'na' THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on Create Table a_form ( form_ID int Not Null, parent_ID int, missingCheckbox bit, missingNote varchar(100), incorrectTxtbox varchar(50), replaceWithTxtbox varchar(50) )
--Target table Trigger inserts into Create Table b_items ( items_ID int Not Null, form_ID int Not Null, parent_ID int, ItemTitle varchar(150) )
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
