.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

Trigger without UPdate,INSERT and DELETE

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 

I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.

Thanks,

MK 

 




View Complete Post


More Related Resource Links

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

  
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?



Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.



For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Allow or deny roles to update, insert and delete records.

  
Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

MS Linq To SQL Samples - Insert/Update/Delete

  
Hi, I'm stumbling along trying my hand at Linq to SQL and have come across this code below that was provided by MS.http://msdn.microsoft.com/en-us/vbasic/bb737928.aspxThe problem is there is no ".add" method for Categories to chose in the statement db2.Categories.add(newCategory)I've seen similar videos here on ASP .NET that uses code very much like the example below, but I am unable to run this example connected to a Northwind SQL database using Web Developer 2010 Express. This is the One-to-Many example listed under Linq To SQL Samples - Insert/Update/Delete. Thanks for any help! Shawn Dim db2 = New DataClassesDataContext Dim ds = New DataLoadOptions() ds.LoadWith(Of Category)(Function(p) p.Products) db2.LoadOptions = ds Dim newCategory = New Category With {.CategoryName = "Widgets", _ .Description = "Widgets are the customer-facing analogues " & _ "to sprockets and cogs.", _ .Picture = Nothing} Dim newProduct = New Product With {.ProductName = "Blue Widget", _ .UnitPrice = 34.56#, _ .Category = newCategory, _

Update, delete, insert

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases. How can I make the second database update, delete and insert?  

Update, Delete, Insert Options

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases.

Insert, Update, Delete with ajax

  

I'm kind of new to ajax. I normally just use  the canlendar and some popup stuffs.

i'm wondering what is the practice to doing Insert, Update, and Delete on  GridView/FormView with ajax.

Is UpdatePanel the best thing i can use it with Insert, Update, and Delete?


How do I control using ListView when to show the Update/Delete/Insert buttons and when not?

  

It should be simple, but I didn't found yet how to do it.

There is only one user (Admin with user name and passowrd, he has a cookie so I know when he is watching the page) I would like to show him the Update/Delete/Insert button options in my ListView, but to the other users I don't want them to be able doing this kind of things, So it should be hidden from them.

How I am doing it in C# code?

Showing/Hiding these buttons?

 


Insert, Update and delete locks

  

Hi,

I looking for the best way to set locks for the following sql commands:

1)

"

Insert

 

into Users

(

UserName

,

Email

,

Pswrd

 

)

values<

SQL Trigger INSERT, UPDATE table name (inserted updated)

  
I am trying to make a trigger for INSERT, UPDATE.  For INSERT you get a table named inserted that you can use, and for UPDATE you get updated.  But what is the name if you doing a trigger for both?

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.

  

Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )


Help required regarding audit (Update/INSERT/DELETE) a table in Database.

  

Hi all

 

Help required regarding audit (Update/INSERT/DELETE) a table in Database.

 

 

I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.

 

Now I want find that who is doing this

 

Insert,Delete Update Operations with XML

  

i already bind my grid with xml file using dataset as dataset has property of reading xml

but now i want to perform operations on xml like Inserting of Data,Deleting of Data and Updating can anyone give me short tutoral how we can perform this thankx you

my xml

<?xml version="1.0" encoding="utf-8" ?>
<Employees>
  <Employee>
    <Id>1</Id>
    <Name>Rockyy</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>2</Id>
    <Name>Slow</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>3</Id>
    <Name>Pop</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>4</Id>
    <Name>Music</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>5</Id>
    <Name>Metal</Name>
    <Sal>2000</Sal>
  </Employee>
</Employees>


Loading with Gridview

  private void loadxml()
    {
        ds.Reset();
        ds.ReadXml(Server.MapPath("testing.xml"));
        GridView1.DataSource=ds;
        GridView1.DataBind();
        //throw new NotImplementedException();
    }


SELECT INSERT DELETE UPDATE happening at the same time

  

Hi All,
I have three table and SELECT DELETE INSERT UPDATE happening at the same time.
Join query is used for SELECT and DELETE.
INSERT and UPDATE no join query.
What are the actions to be taken to avoid deadlock. Will indexing help to resolve the dead lock.
Appreciate somebodys help immediately.

Regards,
Smithesh

 


Record Count on MERGE Statement on Insert,Update,Delete

  

HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

Thanks.

 


khrizz tell

the insert trigger works properly, the delete trigger does not.....what do you think

  

INSERT TRIGGER

ALTER TRIGGER [dbo].[CREER_SOMMAIRE_PARTIE_JOUEUR] ON [dbo].[SOMMAIRE_PARTIE]

FOR INSERT
  as

DECLARE     @NB_BUT int,
            @RESULTAT_VISITEUR int,
            @ID_EQUIPE_VISITEUR int,
            @ID_EQUIPE_LOCAL int,
            @ID_EQUIPE_JOUEUR int,
            @RESULTAT_LOCAL int,
            @NB_PASSE int,
            @NB_MINUTE_PUNITION int,
            @NB_BUT_TOTAL_JOUEUR int,
            @NB_PASSE_TOTAL_JOUEUR int,
            @NB_MINUTE_PUNITION_JOUEUR int


/* Sélectionner le nombre de but inscrit par un joueur */
SELECT @NB_BUT = NB_BUT
FROM inserted SOMMAIRE_PARTIE

/* Sélectionner le résultat avant tout changement de l'équipe visiteur */
SELECT @RESULTAT_VISITEUR = RESULTAT_VISITEUR
FROM PARTIE
    join SOM

select ,insert,update and delete is not getting recorded in the database audit spec.

  

Hi,

I am using SQLSERVER 2008 Standard Edition and i m trying to implement database audit for few database. Following is the script that i used:

CREATE

SERVER AUDIT [audit_mirror]

TO

SECURITY_LOG

WITH

(

QUEUE_DELAY = 1000

How to perform insert, update, delete and select rows in ASP.NET gridview control using stored proce

  
In this article, we will explore how to add, update, delete and select a record from sql server database using gridview control in a simple way. I'm using visual studio 2010 and sql server 2008 for this sample.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend