|
|
You're in the
sql server programming
group:Another Delete Trigger Twist...
sql server programming:
Hi keep getting excellent help so I am trying again. Hopefully, we will have
all our trigger twists resolved soon. Below is the trigger we are using for a variety of tables but I am now getting an error that I don't understand exactly how to solve: CREATE TRIGGER [SaveDeletedCustomer] ON [dbo].[customer] INSTEAD OF DELETE -- Must use INSTEAD OF as the table has many Text columns. AS insert into delcustomer select * from deleted del --Save deleted record in the Delcustomer table DELETE customer FROM deleted where customer.cusid = deleted.cusid EXEC master..xp_sendmail 'Administrator', 'A Customer Record was just DELETED.' The Error states: "Cannot insert a non-null value into a timestamp column. Unse INSERT with a column list or with a default of NULL for the timestamp column. Why we even have a timestamp column I don't know (we have them on a few tables but they don't appear to be updated). So, the big question is how do I fix the delete error. My guess is that you are going to tell me that I have to define each field with is a pain because we have probably 150 or more columns. Thanks, Chris
Hi
To add, If you send an -email from a trigger and the e-mail sending fails, your transaction gets rolled back. Debugging such an issue is very diffucult once in production. Regards Mike [quoted text, click to view] "David Portas" wrote:
> Yes, you should ALWAYS list the column names in an INSERT statement. It's > the only safe way. In Query Analyzer you can drag the column name list from > the Object Browser so you don't need to do any extra typing. > > Don't send email from a trigger. There's no need to hold open a transaction > while the mail is sent. INSERT a row into another table to record the event > and then send out email on a regular schedule as part of a SQL Agent job. > > -- > David Portas > SQL Server MVP > -- > >
Yes, you should ALWAYS list the column names in an INSERT statement. It's
the only safe way. In Query Analyzer you can drag the column name list from the Object Browser so you don't need to do any extra typing. Don't send email from a trigger. There's no need to hold open a transaction while the mail is sent. INSERT a row into another table to record the event and then send out email on a regular schedule as part of a SQL Agent job. -- David Portas SQL Server MVP --
[quoted text, click to view]
On Wed, 10 Nov 2004 21:10:45 -0500, Chris Marsh wrote: >Why we even have a timestamp column I don't know (we have them on a few >tables but they don't appear to be updated). Hi Chris, David has already answered most of your questions, but didn't comment on this remark. A timestamp column is never explicitly updated. It is not even allowed. A timestamp column (also called rowversion column - the name timestamp is grossly misleading, as there is no way to extract a time from the value in these columns) is automatically changed whenever a row is changed by an UPDATE statement. It is also automatically filled when the row is inserted. The raison d'être for a timestamp column is optimistic locking: an application reads a row without locking it, stores the timestamp value internally, shows it on a screen and waits for the user to proceed. If the user changes any values, an UPDATE statement is sent to the server; this statement includes a WHERE timestamp_column = old_timestamp_value. If this where cluase is not matched, no row is affected by the update and the user is informed that his changes could not be saved because another user or process has already changed the row. Best, Hugo --
Thank you! Another question and answer resolved. I appreciate the help.
[quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:VJudnTfig9qDhg7cRVn-3A@giganews.com... > Yes, you should ALWAYS list the column names in an INSERT statement. It's > the only safe way. In Query Analyzer you can drag the column name list > from the Object Browser so you don't need to do any extra typing. > > Don't send email from a trigger. There's no need to hold open a > transaction while the mail is sent. INSERT a row into another table to > record the event and then send out email on a regular schedule as part of > a SQL Agent job. > > -- > David Portas > SQL Server MVP > -- > >
Thank you! Another question and answer resolved. I appreciate the help.
[quoted text, click to view] "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message news:A852FD97-7EBF-4018-BF8A-8E30DECCA2BC@microsoft.com... > Hi > > To add, If you send an -email from a trigger and the e-mail sending fails, > your transaction gets rolled back. Debugging such an issue is very > diffucult > once in production. > > Regards > Mike > > "David Portas" wrote: > >> Yes, you should ALWAYS list the column names in an INSERT statement. It's >> the only safe way. In Query Analyzer you can drag the column name list >> from >> the Object Browser so you don't need to do any extra typing. >> >> Don't send email from a trigger. There's no need to hold open a >> transaction >> while the mail is sent. INSERT a row into another table to record the >> event >> and then send out email on a regular schedule as part of a SQL Agent job. >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> >>
Thank you! Another question and answer resolved. I appreciate the help.
[quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:rpb6p0d8eck3vfaeaecmqjl5478hmmp47k@4ax.com... > On Wed, 10 Nov 2004 21:10:45 -0500, Chris Marsh wrote: > >>Why we even have a timestamp column I don't know (we have them on a few >>tables but they don't appear to be updated). > > Hi Chris, > > David has already answered most of your questions, but didn't comment on > this remark. > > A timestamp column is never explicitly updated. It is not even allowed. A > timestamp column (also called rowversion column - the name timestamp is > grossly misleading, as there is no way to extract a time from the value in > these columns) is automatically changed whenever a row is changed by an > UPDATE statement. It is also automatically filled when the row is > inserted. > > The raison d'être for a timestamp column is optimistic locking: an > application reads a row without locking it, stores the timestamp value > internally, shows it on a screen and waits for the user to proceed. If the > user changes any values, an UPDATE statement is sent to the server; this > statement includes a WHERE timestamp_column = old_timestamp_value. If this > where cluase is not matched, no row is affected by the update and the user > is informed that his changes could not be saved because another user or > process has already changed the row. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
Yes, using the column list is the best approach (if not the only approach).
But you can have the system generate most of the code for you using something like the following: select ' , ' + name from syscolumns where id = object_id('delcustomer ') order by colorder - James [quoted text, click to view] "Chris Marsh" <cmarsh@synergy-intl.com> wrote in message news:%23n2NvM5xEHA.3400@TK2MSFTNGP10.phx.gbl... > Hi keep getting excellent help so I am trying again. Hopefully, we will have > all our trigger twists resolved soon. Below is the trigger we are using for > a variety of tables but I am now getting an error that I don't understand > exactly how to solve: > > CREATE TRIGGER [SaveDeletedCustomer] ON [dbo].[customer] > > INSTEAD OF DELETE -- Must use INSTEAD OF as the table has many Text > columns. > > AS > > insert into delcustomer select * from deleted del --Save deleted record in > the Delcustomer table > > > DELETE customer FROM deleted where customer.cusid = deleted.cusid > > > EXEC master..xp_sendmail 'Administrator', > > 'A Customer Record was just DELETED.' > > The Error states: > > "Cannot insert a non-null value into a timestamp column. Unse INSERT with a > column list or with a default of NULL for the timestamp column. > > Why we even have a timestamp column I don't know (we have them on a few > tables but they don't appear to be updated). So, the big question is how do > I fix the delete error. My guess is that you are going to tell me that I > have to define each field with is a pain because we have probably 150 or > more columns. > > Thanks, > > Chris > >
Another great tip! Thank you!
[quoted text, click to view] "jshotwell" <jshotwell@encorecredit.com> wrote in message news:OigOxrDyEHA.3844@TK2MSFTNGP09.phx.gbl... > Yes, using the column list is the best approach (if not the only > approach). > But you can have the system generate most of the code for you using > something like the following: > > select ' , ' + name > from syscolumns > where id = object_id('delcustomer ') > order by colorder > > - James > > "Chris Marsh" <cmarsh@synergy-intl.com> wrote in message > news:%23n2NvM5xEHA.3400@TK2MSFTNGP10.phx.gbl... >> Hi keep getting excellent help so I am trying again. Hopefully, we will > have >> all our trigger twists resolved soon. Below is the trigger we are using > for >> a variety of tables but I am now getting an error that I don't understand >> exactly how to solve: >> >> CREATE TRIGGER [SaveDeletedCustomer] ON [dbo].[customer] >> >> INSTEAD OF DELETE -- Must use INSTEAD OF as the table has many Text >> columns. >> >> AS >> >> insert into delcustomer select * from deleted del --Save deleted record > in >> the Delcustomer table >> >> >> DELETE customer FROM deleted where customer.cusid = deleted.cusid >> >> >> EXEC master..xp_sendmail 'Administrator', >> >> 'A Customer Record was just DELETED.' >> >> The Error states: >> >> "Cannot insert a non-null value into a timestamp column. Unse INSERT >> with > a >> column list or with a default of NULL for the timestamp column. >> >> Why we even have a timestamp column I don't know (we have them on a few >> tables but they don't appear to be updated). So, the big question is how > do >> I fix the delete error. My guess is that you are going to tell me that I >> have to define each field with is a pain because we have probably 150 or >> more columns. >> >> Thanks, >> >> Chris >> >> > >
Okay, so now I am cooking on my delete triggers, have them setup on about a
dozen key tables and it's working like a champ. It would be really cool to know who has deleted the record and log that in the Delxxxx table. Or, is there another approach that is better. Any example on how I can log a users delete including the date and time. It would really be amazing if I even knew what PC they did it from? Thanks again. [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:VJudnTfig9qDhg7cRVn-3A@giganews.com... > Yes, you should ALWAYS list the column names in an INSERT statement. It's > the only safe way. In Query Analyzer you can drag the column name list > from the Object Browser so you don't need to do any extra typing. > > Don't send email from a trigger. There's no need to hold open a > transaction while the mail is sent. INSERT a row into another table to > record the event and then send out email on a regular schedule as part of > a SQL Agent job. > > -- > David Portas > SQL Server MVP > -- > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |