Global Variable

Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?

What the heck are you doing?

UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.

CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)

CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)

SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END

Example Data and Usage:

insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')

SELECT dbo.udfMyFunction (2)

I don't know that this example is all that useful, but maybe it will give you some ideas.

Regards,

hmscott

Global Triggers?

Hi all,
I have been asked to help design a general audit trail for several
applications that use SQL server.
I would like to create a 'Global Trigger' (for lack of a better term) that
can grab any insert, update or delete on a table across databases on the same
server, and log that data into a seperate database, which would be excluded
from the auditing, since it would create an infinite loop rather quickly.
Is there such a thing? How difficult would this be? I would ASSUME that
this had been tried before. If it is impossible to go this route, is there
something close to this I can do?
We are in the design phase, and this would be a preferred way to go.
Thanks in advance,
Brad Simon
I have been kicking around ideas for a while for something like this as well
and I am curious to follow the other responses. I don't there is such a
thing as a global trigger.
You could put triggers on each table and log the changes into a 'history'
table but you would need a history table for every table you are logging -
maybe an audit db would make more sense. It would not be difficult to write
a code generator to create these history tables and triggers for you
automatically. One limitation of this is that you are only logging Inserts,
Updates, and Deletes. There is no way to log Select. In the audit project
I have been thinking about they want to know who is looking at the data as
well as changing it.
Another thing we are thinking about is a middle tier that would take an XML
formatted parameter and reformat it into a SQL statement and return the
results. The app would pass in all of it's requests as XML and we would
just log the XML. All the business rules and logic to reformat the XML to
SQL would be in this middle tier. The only caveat to this is that it's not
automatic. If someone decides to bypass this component (and security allows
it) then nothing gets logged.
Like I said I am still in the 'kicking around' stage and wondering what else
people have done. There are probably third party apps that hook directly
into SQL and log everything - kind of like Profiler or Trace, but with an
emphasis geared toward audit as opposed to performance and monitoring.
"Brad Simon" <bsimon@.simondeveloping.com> wrote in message
news:E4C9D7B7-AA8E-4663-9C3D-FBC33554D450@.microsoft.com...
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the
> same
> server, and log that data into a seperate database, which would be
> excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is
> there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
> --
> Thanks in advance,
> Brad Simon
|||Brad Simon wrote:
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the same
> server, and log that data into a seperate database, which would be excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
>
There's no such beast as a global trigger. You'd have to install
triggers on every table you wanted to audit and have them communicate
with an centralized stored procedure in an audit database. But, by the
time you gathered all the necessary information which is available only
in the trigger, the centralized stored procedure wouldn't have much to do.
You can write generic trigger code which tries to dynamically determine
the columns on the table the trigger is installed on (so that you have a
single trigger script which figures out the table it is attached to at
runtime), but this is extremely inefficient and will unnecessarily
degrade performance as the trigger does runtime discovery of columns
every single time a data chaneg occurs.
You really can't avoid writing table-specific triggers for every table
you want to audit, hard-coded to the columns in each table. Your best
bet would be to write some trigger-generating code which sniffs out a
table's columns from metadata and produces a custom CREATE TRIGGER
script for that table.
You can have a look at our OmniAudit product which will do all the work
of creating and installing audit triggers on all tables automatically.
Steve Troxell
http://www.krell-software.com

Global Triggers?

Hi all,
I have been asked to help design a general audit trail for several
applications that use SQL server.
I would like to create a 'Global Trigger' (for lack of a better term) that
can grab any insert, update or delete on a table across databases on the same
server, and log that data into a seperate database, which would be excluded
from the auditing, since it would create an infinite loop rather quickly.
Is there such a thing? How difficult would this be? I would ASSUME that
this had been tried before. If it is impossible to go this route, is there
something close to this I can do?
We are in the design phase, and this would be a preferred way to go.
--
Thanks in advance,
Brad SimonI have been kicking around ideas for a while for something like this as well
and I am curious to follow the other responses. I don't there is such a
thing as a global trigger.
You could put triggers on each table and log the changes into a 'history'
table but you would need a history table for every table you are logging -
maybe an audit db would make more sense. It would not be difficult to write
a code generator to create these history tables and triggers for you
automatically. One limitation of this is that you are only logging Inserts,
Updates, and Deletes. There is no way to log Select. In the audit project
I have been thinking about they want to know who is looking at the data as
well as changing it.
Another thing we are thinking about is a middle tier that would take an XML
formatted parameter and reformat it into a SQL statement and return the
results. The app would pass in all of it's requests as XML and we would
just log the XML. All the business rules and logic to reformat the XML to
SQL would be in this middle tier. The only caveat to this is that it's not
automatic. If someone decides to bypass this component (and security allows
it) then nothing gets logged.
Like I said I am still in the 'kicking around' stage and wondering what else
people have done. There are probably third party apps that hook directly
into SQL and log everything - kind of like Profiler or Trace, but with an
emphasis geared toward audit as opposed to performance and monitoring.
"Brad Simon" <bsimon@.simondeveloping.com> wrote in message
news:E4C9D7B7-AA8E-4663-9C3D-FBC33554D450@.microsoft.com...
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the
> same
> server, and log that data into a seperate database, which would be
> excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is
> there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
> --
> Thanks in advance,
> Brad Simon|||Brad Simon wrote:
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the same
> server, and log that data into a seperate database, which would be excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
>
There's no such beast as a global trigger. You'd have to install
triggers on every table you wanted to audit and have them communicate
with an centralized stored procedure in an audit database. But, by the
time you gathered all the necessary information which is available only
in the trigger, the centralized stored procedure wouldn't have much to do.
You can write generic trigger code which tries to dynamically determine
the columns on the table the trigger is installed on (so that you have a
single trigger script which figures out the table it is attached to at
runtime), but this is extremely inefficient and will unnecessarily
degrade performance as the trigger does runtime discovery of columns
every single time a data chaneg occurs.
You really can't avoid writing table-specific triggers for every table
you want to audit, hard-coded to the columns in each table. Your best
bet would be to write some trigger-generating code which sniffs out a
table's columns from metadata and produces a custom CREATE TRIGGER
script for that table.
You can have a look at our OmniAudit product which will do all the work
of creating and installing audit triggers on all tables automatically.
Steve Troxell
http://www.krell-software.com

Global Triggers?

Hi all,
I have been asked to help design a general audit trail for several
applications that use SQL server.
I would like to create a 'Global Trigger' (for lack of a better term) that
can grab any insert, update or delete on a table across databases on the sam
e
server, and log that data into a seperate database, which would be excluded
from the auditing, since it would create an infinite loop rather quickly.
Is there such a thing? How difficult would this be? I would ASSUME that
this had been tried before. If it is impossible to go this route, is there
something close to this I can do?
We are in the design phase, and this would be a preferred way to go.
Thanks in advance,
Brad SimonI have been kicking around ideas for a while for something like this as well
and I am curious to follow the other responses. I don't there is such a
thing as a global trigger.
You could put triggers on each table and log the changes into a 'history'
table but you would need a history table for every table you are logging -
maybe an audit db would make more sense. It would not be difficult to write
a code generator to create these history tables and triggers for you
automatically. One limitation of this is that you are only logging Inserts,
Updates, and Deletes. There is no way to log Select. In the audit project
I have been thinking about they want to know who is looking at the data as
well as changing it.
Another thing we are thinking about is a middle tier that would take an XML
formatted parameter and reformat it into a SQL statement and return the
results. The app would pass in all of it's requests as XML and we would
just log the XML. All the business rules and logic to reformat the XML to
SQL would be in this middle tier. The only caveat to this is that it's not
automatic. If someone decides to bypass this component (and security allows
it) then nothing gets logged.
Like I said I am still in the 'kicking around' stage and wondering what else
people have done. There are probably third party apps that hook directly
into SQL and log everything - kind of like Profiler or Trace, but with an
emphasis geared toward audit as opposed to performance and monitoring.
"Brad Simon" <bsimon@.simondeveloping.com> wrote in message
news:E4C9D7B7-AA8E-4663-9C3D-FBC33554D450@.microsoft.com...
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the
> same
> server, and log that data into a seperate database, which would be
> excluded
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is
> there
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
> --
> Thanks in advance,
> Brad Simon|||Brad Simon wrote:
> Hi all,
> I have been asked to help design a general audit trail for several
> applications that use SQL server.
> I would like to create a 'Global Trigger' (for lack of a better term) that
> can grab any insert, update or delete on a table across databases on the s
ame
> server, and log that data into a seperate database, which would be exclude
d
> from the auditing, since it would create an infinite loop rather quickly.
> Is there such a thing? How difficult would this be? I would ASSUME that
> this had been tried before. If it is impossible to go this route, is ther
e
> something close to this I can do?
> We are in the design phase, and this would be a preferred way to go.
>
There's no such beast as a global trigger. You'd have to install
triggers on every table you wanted to audit and have them communicate
with an centralized stored procedure in an audit database. But, by the
time you gathered all the necessary information which is available only
in the trigger, the centralized stored procedure wouldn't have much to do.
You can write generic trigger code which tries to dynamically determine
the columns on the table the trigger is installed on (so that you have a
single trigger script which figures out the table it is attached to at
runtime), but this is extremely inefficient and will unnecessarily
degrade performance as the trigger does runtime discovery of columns
every single time a data chaneg occurs.
You really can't avoid writing table-specific triggers for every table
you want to audit, hard-coded to the columns in each table. Your best
bet would be to write some trigger-generating code which sniffs out a
table's columns from metadata and produces a custom CREATE TRIGGER
script for that table.
You can have a look at our OmniAudit product which will do all the work
of creating and installing audit triggers on all tables automatically.
Steve Troxell
http://www.krell-software.com

Giving users specific DDL permissions

I have an archival process on a large database that runs once a month.
At the beginning of the process the triggers and indexes on the
tables whose data is moved are dropped, the data is moved and then the
triggers and indexes are recreated at the end. This produces a
massive improvement in performance.

The problem is the process is supposed to run on users accounts (thats
the way the front-end is set up) and they don't have the neccessary
permissions to drop & create triggers & indexes. I can't see any way
to give them permissions only on specific tables or triggers/indexes.
Nor does giving them permissions to the stored procedures that do the
dropping & re-creating work, DDL permissions don't seem to be
inherited the way they are with tables.

Is blanket rights to drop & create objects through the db_ddladmin
role the only way users can get rights?

Thanks,

K FineganK Finegan (KevinFinegan@.Hotmail.com) writes:
> I have an archival process on a large database that runs once a month.
> At the beginning of the process the triggers and indexes on the
> tables whose data is moved are dropped, the data is moved and then the
> triggers and indexes are recreated at the end. This produces a
> massive improvement in performance.
> The problem is the process is supposed to run on users accounts (thats
> the way the front-end is set up) and they don't have the neccessary
> permissions to drop & create triggers & indexes. I can't see any way
> to give them permissions only on specific tables or triggers/indexes.
> Nor does giving them permissions to the stored procedures that do the
> dropping & re-creating work, DDL permissions don't seem to be
> inherited the way they are with tables.
> Is blanket rights to drop & create objects through the db_ddladmin
> role the only way users can get rights?

In SQL2000, yes. The upcoming version of SQL Server has some more
possibilities.

As for the triggers, it's probably better to say ALTER TABLE DISABLE
TRIGGERS ALL, than to drop them. Not that this addresses the permissions
problem.

There is a way to have a trigger off-turnable by means of regular
permissions though. In the trigger body you do this:

IF object_id('tempdb..#reloading') IS NULL
... Trigger logic comes here.

In you process you would create this temp table. The test would save you
the logic of the trigger, but you may still have an overhead, because
SQL Server has to run the statement as if there trigger was active.

No, for indexes I don't have any tricks.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I like the trigger trick, will probably use it &
DISABLE which I didn't know about. Pity about the indexes. For the
moment I suppose I'll have to give one (trustworthy) user ddl_admin
rights.

K Finegan