Creating A Trigger To Check Before Deleting A Record
Jun 12, 2008
I am using the tables created by the aspnet_regsql.exe tool for security. Basically, I need to ensure that an account named Administrator is never deleted. I also have a role named administrator, and I need to make sure that Administrator is never removed from the administrator role.
Can I create a trigger to ensure that the Administrator is never deleted and that the Administrator is never removed from the Administrator role? I know it will probably be two separate triggers: one on the aspnet_users table and one on the aspnet_usersinroles table.
Thanks a lot for the help!
View 1 Replies
ADVERTISEMENT
May 21, 2007
I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.
I'm kind of new to SQL, but I'm learning as much as I can.
I created a web form that sends a new record to a SQL 2005 table I setup.
This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.
I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.
My Database is called Engineering
The table is called ESSPartNumLog
And I have the following Columns that I would like to send in my email..
ESSSequence (PK,int, not null)
MaterialType (nvarchar(255, null)
ESSPrefix (nvarchar(255, null)
PartDescription (nvarchar(255, null)
Project (nvarchar(255, null)
PM (nvarchar(255, null)
Any ideas, or can you point me in the right direction?
View 1 Replies
View Related
Oct 9, 2007
I have three tables
1. membership table(aspnet_membership table)
2. User Contacts table
3. Address table
These three table have relation ship with one another through a UserId field.
How will you set up cascaded delete on these tables, Like if I delete a user in the membership table I want the related records in the other tables to be deleted as well. Cascaded delete is it something done through code, or is it definde when the tables are created.
Please advice.
View 1 Replies
View Related
Jun 23, 2005
Hey Guys,I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records. I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one. Wondering if anyone would know how I could do this?
View 3 Replies
View Related
Nov 19, 2004
hi to all,
How to delete duplicate record in the recordset?
Thanks...
View 3 Replies
View Related
Feb 27, 2006
Hi SQL gurus,
I have a form that depending on the outcome, will either add, update or delete a record.
1) If there IS NO record of "this" and "that" and Request("x") <> "" it will add a new record.
2) If there IS a record for "this" and "that" and Request("x") <> "" it will update column 1,2,3 or 4.
3) If there is a record for "this" and "that" and Request("x") = "" it will delete the record.
My problem is if there is a value in request("1") it works fine, but if there is no value in ("1") and there is a value in 2, 3, or 4 it will delete the record.
If request("1") <> "" OR request("2") <> "" OR request("3") <> "" OR request("4") <> "" Then
sSQL = "SELECT * FROM some_column WHERE this = '" & request("this) & "' and that = '" & request("that) & "'"
oRS.Open sSQL,oConn,adOpenKeySet,adLockOptimistic
If oRS.EOF Then
oRS.Addnew
oRS.Fields("this") = request("this")
ors.Fields("that") = request("that")
Else
ors.Movefirst
End If
oRS.fields("1") = request("1")
oRS.fields("2") = request("2")
oRS.fields("3") = request("3")
oRS.fields("4") = request("4")
oRS.Update
oRS.Close
Else
sSQL = "Delete from some_table Where this = '" & '" & request("this) & "' & "' AND that = '" & '" & request("that) & "' & "'"
objCmd.ActiveConnection = oConn
objCmd.CommandType = adCmdText
objCmd.CommandText = sSQL
objCmd.Execute
End if
Hope I explained that okay.
View 1 Replies
View Related
Jul 20, 2005
Hi All,I am using Microsoft SQL Enterprise Manager version 8.0 and havecreated a view from a combination of 4 different tables. I would liketo be able to go into sql and open the view and select a row anddelete that row however this seem impossible right now. I am not sureif it's possible to delete a row from a view?? Or could it be thatthese tables are all interconnected and in order to delete a recordthat is joined to one or more of the tables it has to be deleted atthe top level of the join heirarchy etc etc. (do you understand what imean?) Can this be done??Thanks in advance,Erin
View 4 Replies
View Related
Nov 14, 2007
Consider this scenario.
I have two database in the sql server and consider that i have a query which has 4 tables inner joined.
When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the
no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge
list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be
appericated.
View 3 Replies
View Related
Nov 25, 2006
hai dears i want to delete a row from the table on the basis of key sent in beusers obj
"connection myConn = new connection();" this is ma own class that establishes the connetion which works fine in other caseseg insert
'' it shows no error or exception ' but do not delete record
so plz tell me how to do this...
or whats wrong in this
public void delete(BEusers obj)
{
connection myConn = new connection();
oCommand = new SqlCommand("sp_delete_tbl_users_by_userid",myConn.sqlCon);
oCommand.CommandType = CommandType.StoredProcedure;
try {
myConn.OpenConnection();
sp = oCommand.Parameters.Add("@User_Id", SqlDbType.VarChar);
sp.Size = 20;
sp.Value = obj.user_id;
oCommand.ExecuteNonQuery();
}
catch (Exception oException)
{ throw oException; }
finally { myConn.CloseConnection(); }
}
View 3 Replies
View Related
Aug 26, 2005
Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.
View 1 Replies
View Related
Mar 26, 2008
Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like
recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...
please suggest
thank you
View 2 Replies
View Related
Oct 4, 2006
Hi,
I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted." Method ReadFirst() throws similar exception as well.
More details:
1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)
2. Let's say, there is no records in the resultset. Insert two new ones. Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.
3. Then you can call ReadFirst() or Read with the same result.
4. Now call ReadAbsolute(0). It returns true. Call HasRows, ReadFirst() or Read() and you will get the exception each time.
I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet. But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.
Could anybody tell me what the problem is?
Thank you.
View 7 Replies
View Related
Oct 3, 2006
Hi,
I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted." Method ReadFirst() throws similar exception as well.
More details:
1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)
2. Let's say, there is no records in the resultset. Insert two new ones. Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.
3. Then you can call ReadFirst() or Read with the same result.
4. Now call ReadAbsolute(0). It returns true. Call HasRows, ReadFirst() or Read() and you will get the exception each time.
I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet. But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.
Could anybody tell me what the problem is?
Thank you.
View 1 Replies
View Related
May 31, 2002
Hello everybody,
I have problem deleting row from same table using below trigger
CREATE TRIGGER [tDeleteDomain] ON dbo.iMS_Domains
FOR DELETE
AS
IF @@RowCount > 1
BEGIN
ROLLBACK TRAN
RAISERROR ('You can only delete one domain at a time.', 16, 10)
END
DECLARE@DomainID int
SELECT@DomainID = ID
FROMDELETED
DELETE FROMiMS_Domains
WHEREAliasFor = @DomainID
If I am using this trigger I am getting the below error
Server: Msg 217, Level 16, State 1, Procedure tDeleteid, Line 16
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Can anybody give good suggestiong regarding this.
Thanks,
ravi
View 6 Replies
View Related
Oct 9, 2007
I can not get this stored procedure to delete my records...
I have a
contact table
RecordID
FirstName
LastName
etc
and a Address table
RecordID
Street
Zip
Town
Country
And a Relation table
RecordID
ContactID
AddressID
CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
Can someone please post me an advice?
View 5 Replies
View Related
Mar 11, 1999
I can't delete records for a SQL Server table when I attached the table to Microsoft Access 97. I time out when I try to delete the record. However I can query the table.
I use the standard ODBC setup, do I need to do anything else beside using the standard Access link.
Thanks in advance
View 1 Replies
View Related
Aug 30, 2013
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
View 1 Replies
View Related
Jun 18, 2007
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
View 2 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 Replies
View Related
May 12, 2014
,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.
Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356
Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?
View 8 Replies
View Related
Feb 1, 2007
Hello,I created the following SQL script to check if a record exists:IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@LevelName) = LOWER(LevelName))) Return (1)ELSE Return (0)And I also found in a web page another solution:IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@LevelName) SELECT 1ELSE SELECT 0- Which approach should I use?- Why "SELECT 1 FROM"?- And when should I use SELECT or RETURN?All I need is to know if the record exists ... nothing else.I will use this procedure on an ASP.NET 2.0 / C# web site.I am not sure if this important but anyway ...Thank You,Miguel
View 6 Replies
View Related
Dec 24, 2007
Hi,
I was wondering if someone can help.
In vb.net what is the best way to check if a record exists if you are using an sql data reader?
For my application I need to display a button control (make it visible on the page) if a record is available after executing my sql select statement.
cheers
Mark :)
View 3 Replies
View Related
Apr 16, 2007
Hi,
Any one can help me with the following requirement:-
. Checking the record if its lock before issues any statement
Any sample will be helpful
Best Regards
View 2 Replies
View Related
Nov 25, 2006
Aperently I could not insert a text field to another table from INSERTED in a trigger.It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText) SELECT i.myTrID, i.myFirstName, p.myBigText FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID WHERE (i.myTrType = 'In') Thanks,
View 1 Replies
View Related
Nov 25, 2006
Apparently I could not insert a text field to another table from INSERTED in a trigger.
It seems the following is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
View 1 Replies
View Related
Sep 4, 2006
Hi,
How can I check if there is trigger for one table, and if yes disable?
if ( there is trigger in cm_template = yes )
ALTER TABLE cm_template DISABLE TRIGGER TRG_cm_template
else
do stuff...
Since I try disable one trigger in a table which there is not trigger I think I'll receive one error. am I right?
cheers,
View 1 Replies
View Related
Feb 17, 2004
Before adding a record this must check that in the column machine_id not twice the same machines are listed
Can someone help me?
Thanx and cheerz Wim
View 1 Replies
View Related
May 17, 2005
Hi,
Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?
As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.
I guess I could use try, catch within the .APSX page or would a stored procedure be better?
Thanks
Brett
View 7 Replies
View Related
May 22, 2008
Hi all,
I wanted to check the previous and next record values.
For example:
sKey NextKey PreviousKey
1 2 Null
2 8 1
8 5 2
5 null 8
I wanted to check the value of NextKey of Prev record and Skey of Next record.
Any idea?
Regards
Helen
View 5 Replies
View Related
Mar 6, 2006
How to check if a record is already in the database I want to import to before importing?
My table have 2 primary key, and the DTS is suppose to be schedule to run every night, though it can be run whenever a user want to get the most updated data.
View 3 Replies
View Related
May 22, 2008
Hi all,
I wanted to check the previous and next record values.
For example:
sKey NextKey PreviousKey
1 2 Null
2 8 1
8 5 2
5 null 8
Ex : In the first record of the table, the NextKey is pointing to 2.
So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8.
Now I need to check whether the NextKey and SKey are correct for all rows.
For that I need to check the previous record of "Next key" and next record of "Skey".
Any idea?
Regards
Helen
View 5 Replies
View Related
Aug 7, 2014
I want to incorporate a Check constraint within a trigger, based on this but im struggling with the coding.Assuming that is an Alphanumeric field you should be checking what value is in the alphanumeric column from inserted, comparing it with whatever is in the AMGR_User_Fields_Tbl to see if there’s a duplicate and then raising the error...This is my Trigger:
ALTER TRIGGER [dbo].[DUPLICATES]
ON [dbo].[AMGR_User_Fields_Tbl]
FOR INSERT, UPDATE
AS
DECLARE @Alphanumericcol VARCHAR (750)
-- This trigger has been created to check that duplicate rows are not inserted into table.
-- Check if row exists
SELECT @Alphanumericcol
FROM Inserted i, AMGR_User_Fields_Tbl t
[code]....
View 1 Replies
View Related
Mar 19, 2007
Hello all.
Can anyone advise me how to check to see if a particual triggers exists through a SQL script?
Thanks
View 3 Replies
View Related
