Prevent Single Row Delete With Trigger

Jan 16, 2015

I have this table:

CREATE TABLE Workspaces (
AreaNr CHAR(2)
CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
ON DELETE CASCADE

[Code] ....

Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.

Current trigger:

CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS

BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;

[Code] ....

Desired result: I want to be able to prevent a delete on a single row on the table above.

View 1 Replies


ADVERTISEMENT

Prevent One Single Row From Being Updated

Nov 8, 2006

I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne

View 1 Replies View Related

Prevent DELETE And/or UPDATE

Jul 23, 2005

Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO

View 1 Replies View Related

Trigger To Prevent Duplicates

Aug 21, 2001

Hi all,

I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.

If I create a table with 2 columns

CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)


Then create a trigger to prevent duplicates

CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

Then insert a row into the new table (no other data is in there)

INSERT Table1
VALUES('0691040176','AUG')

I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.


Please help!!!


Thanks

Neill

View 1 Replies View Related

Prevent Trigger From Firing

Jan 13, 2004

Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View 5 Replies View Related

Trigger To Prevent Update If No Changes?

Jul 10, 2013

Is it possible to create a trigger to prevent update of a row if no signifiant colums are updated

Example : I need to allow the update only if colums a,b or c have changes ?

how it should works to keep or discard the update according to condition ?

View 1 Replies View Related

SQL Server 2012 :: Trigger To Prevent Logon To A Database

Mar 19, 2014

OK, I know about this: [URL] ....

But the script has "ALL SERVER".

What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".

View 1 Replies View Related

Allow Single Row Delete From A Table But Not Bulk Delete

Sep 16, 2013

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS

[code]...

When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

View 3 Replies View Related

Create Trigger That Prevent Order Being Processed If The Amount Is 0 In Storage

Feb 9, 2006

When i process a order in table Orders_t i would like to check in storage_t if we defenetly have it in storage. .... if we have it in storage, i decrease the "amount" by 1 ..(amount -1), and process the order. Otherwise it will return nothing.

This is what i´ve come up with so far:


CREATE TRIGGER checkInStorage
ON orders_t
FOR INSERT, UPDATE

AS
DECLARE
@tOrderId char(3),
BEGIN
SET @tOrderId = (SELECT orderId FROM INSERTED)

--check if the amount in storage

IF EXIST(SELECT amount FROM storage_t WHERE orderId = @tOrderId and amount >= 0)
BEGIN --if it return true, i update the storage by decrease the amount with one
UPDATE storage_t
SET (amount = amount - 1)
WHERE orderId = @tOrderId
END




this doesn´t work...

View 6 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh


View 6 Replies View Related

Can't Delete Single Record? HELP!

May 28, 2004

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIA

View 12 Replies View Related

DELETE In One Single Line...???

Sep 29, 2007

Hi,
I am executing the folowing statements in my stored procedure, I want to ask that can all these staments will be executed and combined in a single line of code, meaning can we run all these statemnts in a single line. if not, then any other optimized way to perform this task and other way which will execute with less load on the server.





Code Block

SET NOCOUNT ON
TRUNCATE TABLE WebNew_T_Dump
TRUNCATE TABLE WebNew_TOD_Dump
TRUNCATE TABLE WebNew_T_S_Dump
TRUNCATE TABLE WebNew_AT_Dump
TRUNCATE TABLE WebNew_MMM_Dump
TRUNCATE TABLE WebNew_MMT_Dump
TRUNCATE TABLE WebNew_TC_Dump
TRUNCATE TABLE WebNew_TRW_Dump
TRUNCATE TABLE WebNew_SM_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TL_Dump
TRUNCATE TABLE WebNew_TLC_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TP_Dump
TRUNCATE TABLE WebNew_TSS_Dump
TRUNCATE TABLE WebNew_IT_Dump
Thanks,

View 3 Replies View Related

Single Statement To Delete Record Into More Tables

Aug 6, 2004

Hi ,

I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?

For example if I've two tables involved by join

DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID

I Must use two statement to remove records from both the tables?

Thx

View 3 Replies View Related

Insert Delete Modify In A Single Stored Procedure

Jun 13, 2008

 hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.  

View 1 Replies View Related

Can I Disable Trigger For A Single Sproc Only?

Jun 9, 2008

Hi

So, I know, after searching these forums, that it is possible to disable a trigger before updating a table and then enabling it again afterwords, for instance, from a stored procedure.

I might be dealing with hypotheticals here, but when I do a ...

ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }

... from a stored procedure, will it not be database wide?
Should I worry about another change to the table happening in the timespan in which the trigger is disabled (which it would be for during a single update), which the trigger should have caught?

Regards, Egil.

View 1 Replies View Related

Multiple Inserts In Single Trigger

Feb 10, 2014

I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS

[code]....

I m getting some syntactical errors.

View 6 Replies View Related

Reporting Services :: How To Delete A Single Record From 2 Tables At A Time

May 26, 2015

How to delete a single  record from 2 tables at a time.

View 3 Replies View Related

How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

Mar 18, 2008

This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete from tableName where Date_Stamp = '2008-3-18'

View 7 Replies View Related

DELETE Trigger

Jun 14, 2002

Hi,

In Oracle, we have FOR EACH ROW Concept in triggers which can be used to manipulate to fire the trigger for each row.

How can we implement the same in SQL Server 7.0 ?

View 1 Replies View Related

Delete Trigger

Apr 23, 2001

Hello,
For SQL Server 6.5.
I need help if there is a way to fire a delete trigger when accidentenlty somebody deletes a record from say table AAA then this record should be
inserted in another dump table say Table BBBDump with table schema as table AAA plus some other columns like DB_NAME,Time when recorded
deleted from table AAA, user ID .


Thanks
Rajiv

View 1 Replies View Related

Delete Trigger

Nov 5, 2001

hi, I have a table as follow:

id amount
1 100
1 100
1 200
1 300

I put a deleted trigger on this table when a record is deleted, I update a balance in another table based on the deleted amount. This works fine when I am deleting one record at a time, but when I delete more than one record, the first value from the first record is captured to update the balance due and the rest of the of the records has not effect.
To elaborat more,
delete * from table1 where id =1 and amount =200 -- this works fine
delete * from table1 where id =1 and amount =100 -- the first 100 only update the balance in another table the second 100 does nothing,
any ideas, I appreciate your help.

Thanks

Ali

View 3 Replies View Related

Help On Trigger For Delete

Dec 13, 2004

I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @mdias as int
declare @mFKFeria as int
declare @mtipo as char(1)
declare @mfkcontacto as varchar(7)

if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @mdias -1 where FKFeria= @mFKFeria and FKContacto=@mFKContacto and Tipo=@mTipo
end




Thanks in advanced.

View 3 Replies View Related

Instead Of Delete Trigger

Apr 28, 2008

i have created a view from two tables in my database (ProjectedOutputs, and Output)

since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions

CREATE TRIGGER Test
ON V_ProjectOutputs
INSTEAD OF DELETE
AS

DELETE PROJECTEDOUTPUTS
FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID

View 12 Replies View Related

Delete Trigger && COM+

Feb 27, 2004

Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."

Here follows the code. Hopefully anybody can help me with this problem.

SQL-statement:

Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
End Function

And then the place where the error occurs.

GetConnection cnConn
strSQL = getDeleteRequestSQL(reqId)
10 cnConn.Execute strSQL, , adExecuteNoRecords

And finaly the trigger:

create trigger td_thmld2 on thmld2 for delete as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)

select @numrows = @@rowcount
if @numrows = 0
return

select @numrows = (select count(*) from trigstat with (nolock) )
if @numrows > 0
return

save transaction trans_td_thmld2 <-- REASON FOR ERROR?

/* delete all children in "thmstat" */
delete thmstat
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode

/* delete all children in "thmldlk1" */
delete thmldlk1
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode

/* errors handling */
error:
raiserror @errno @errmsg
rollback transaction trans_td_thmld2
end

View 2 Replies View Related

DELETE TRIGGER

Apr 16, 2008

I'm trying to capture 'what' is deleting records from my tables.

I know what the data is but how do I know which user did it or which stored procedure caused it?

View 1 Replies View Related

SQL Trigger Delete

May 13, 2008

Trying to create a trigger on a table that will delete any records in it that do not exist in another table.

Inserting into OrderRebateHistory Table.
if ordtype, ord_no, and line_seq_no do not exist in oelinhst then delete from OrderRebateHistory or do not insert.

Right now my code will insert the record. Then when another record gets inserted the previous record is deleted.

CREATE TRIGGER [DeletefromOrderRebateHistory] ON [dbo].[OrderRebateHistory]
for INSERT
AS

DELETE OrderRebateHistory
FROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type and
oelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_no
where oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_no
and oelinhst_sql.line_seq_no <> OrderRebateHistory.line_seq_no

View 11 Replies View Related

Trigger After Delete - I Would Need A Trigger Before Delete

Jul 17, 2007

hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in another tabletblA records are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh

View 1 Replies View Related

Trigger After Delete - I Need A Trigger Before Delete

Jul 18, 2007

hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in table tblArecords are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC. This view vwC is based on a key field used in tblA.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh

View 8 Replies View Related

Delete Trigger

Aug 15, 2006

Hi how can i use delete trigger?

For example i've a two table like emp and emp_personal and now what i want to do is

i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?

Ex:

emp emp_personal

emp_id emp_name emp_basic emp_id emp_address

101 Nagu 32,000 101 India

102 Vijay 35,000 102 South Africa

103 Ritesh 30,000 103 U.S

I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?

Is it possible with triggers?

Thanx - Nagu

View 3 Replies View Related

Creating Trigger For A Single Column/Field?

Apr 15, 2008



Hi all,

My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks

Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()

BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

View 3 Replies View Related

Trigger On Delete Statement

Sep 26, 2007

Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks!
James

View 2 Replies View Related

Why Does The DELETE Trigger Not Work ?

Dec 1, 2004

I have the following delete trigger but it doesn't work.


ALTER TRIGGER Users_DeleteUsers
ON dbo.Users
FOR DELETE
AS
DELETE FROM InstantForum_Members WHERE MemberID IN (SELECT ForumMemberId from Deleted)
DELETE FROM InstantKB_Users WHERE UserID IN (SELECT KBMemberId from Deleted)


But when I delete a user from Users table, I get an error in this trigger saying no commit or rollback given in trigger.

Can someone tell me why this trigger will fail ?

View 2 Replies View Related

Conditional Delete Trigger

Feb 15, 2005

On the database that I am maintaining we are having some data integrity issues between our Logon table and another sub table that stores the LogonId.

The best solution would be to put in a foreign key, but that is going to require a lot of work and a lot of code changes for the entire system. This is what we plan to do, but this is not a quick fix. We need something that can be implemented quickly.

The easiest and quickest fix is to check this sub table to see if the LogonId is in the sub table and the row is marked as Active or Working. If it is then we will abort the deletion and raise an error. Otherwise the delete should happen normally.

Is aborting the deletion as simple as :

<code>
Delete From deleted
Where LogonId = @myId
</code>

Or am I missing something?

Thanks for the help,
Tim

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved