nested update triggers

HI all,
I have a question about update triggers. I have a update trigger that
updates a 'dupdated' column to the current date and time. I this so that I
have an accurate record of when the row was updated. due to my mistake,
users where changing the dupdated column to cover their sloppiness.
My question is if I have an update command in an update trigger, will it
recursively call itself.
Thanks
RobertRobert Bravery wrote:

> HI all,
> I have a question about update triggers. I have a update trigger that
> updates a 'dupdated' column to the current date and time. I this so that I
> have an accurate record of when the row was updated. due to my mistake,
> users where changing the dupdated column to cover their sloppiness.
> My question is if I have an update command in an update trigger, will it
> recursively call itself.
> Thanks
> Robert
Not if you have set recursive triggers off (which is also the default
state):
ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
If you allow users to update tables directly then I wouldn't expect a
dependable audit trail. Triggers aren't the whole answer - a better
security implementation is the answer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
Thanks David,
Yes, this table was created durring my learning process, I'm still learning
but was very very green at the time
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139304916.973413.224930@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
>
I
> Not if you have set recursive triggers off (which is also the default
> state):
> ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
> If you allow users to update tables directly then I wouldn't expect a
> dependable audit trail. Triggers aren't the whole answer - a better
> security implementation is the answer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql

Needed Help

I need to write a query which should calculate the Month and year part of
the Date that is stored in the database. Please note that the query also has
a group by column. Here Date is stored in table c. Please note that the quer
y
works fine and able able to retrieve the Month part. What am interested is
"Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number 6,
which I am getting now.
Apprecite your help.
Query is given below.
SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, MONTH(c.Date)Hi Scott
SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date)
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Scott" <scott@.gmail.com> wrote in message
news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
> I need to write a query which should calculate the Month and year part of
> the Date that is stored in the database. Please note that the query also
> has
> a group by column. Here Date is stored in table c. Please note that the
> query
> works fine and able able to retrieve the Month part. What am interested is
> "Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number
> 6,
> which I am getting now.
> Apprecite your help.
> Query is given below.
> SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, MONTH(c.Date)|||> GROUP BY a.Col1, dataname(month,c.Date)
oops!
Jan2005 and Jan2006 are same as dataname(month,c.Date) results same january
for both if col1 is same.
You need derived table to group by c.date
Regards
R.D
"GregO" wrote:

> Hi Scott
> SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date)
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Scott" <scott@.gmail.com> wrote in message
> news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
>
>|||Hi R.D.
Spot on mate I was a bit quick on that./ He was after Jan 2006 not just Jan
SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
[MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
convert(varchar(4),year(c.Date))
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
> oops!
> Jan2005 and Jan2006 are same as dataname(month,c.Date) results same
> january
> for both if col1 is same.
> You need derived table to group by c.date
> Regards
> R.D
>
> "GregO" wrote:
>|||GregO
what abt feb to august between 2005 and 2006. I did not mean jan. It was an
instance.
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>|||Hi R.D.
I must not be understanding you correctly. The query belwo will result in
group by Maonthname + Year so the months will not be the same. Hard to show
without the data but say the .Date col have
2005-01-01
2005-02-02
2006-01-01
2006-02-02
the results would be
January 2005
Febuary 2005
January 2006
Febuary 2006
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
> GregO
> what abt feb to august between 2005 and 2006. I did not mean jan. It was
> an
> instance.
> Regards
> R.D
> "GregO" wrote:
>|||Good
"GregO" wrote:

> Hi R.D.
> I must not be understanding you correctly. The query belwo will result in
> group by Maonthname + Year so the months will not be the same. Hard to sh
ow
> without the data but say the .Date col have
> 2005-01-01
> 2005-02-02
> 2006-01-01
> 2006-02-02
> the results would be
> January 2005
> Febuary 2005
> January 2006
> Febuary 2006
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
>
>|||Hi Chandra
Yes you can
declare @.myvar nvarchar(10)
declare @.myint bigint
select @.myvar = '9584'
select @.myint = cast(@.myvar as bigint)
print @.myint
--Result
9584
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>