Database Users and Triggers

I created a trigger on a table T1 for insert and update. The trigger
selects values from table T2, which happens to be in a different
database. It seems I have to create users in that other database for
every user who updates or inserts records into T1. Is there any way
around this, using views or stored procedures, in SQL Server 2000?
Thanks
TimoHi Timo,
I don't think you'll be able to do what you want.
Trigger is running in security context of a user who fired it. As so, select
statement on T2 table is run as that user and user must have proper
permissions.
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Timo" <timo@.org.org> wrote in message
news:eBj$lRp9FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I created a trigger on a table T1 for insert and update. The trigger
>selects values from table T2, which happens to be in a different database.
>It seems I have to create users in that other database for every user who
>updates or inserts records into T1. Is there any way around this, using
>views or stored procedures, in SQL Server 2000?
> Thanks
> Timo|||Hi,
please refer article FYI :
http://www.netdscure.co.in/Articles/AuditDML.htm
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Timo" wrote:

> I created a trigger on a table T1 for insert and update. The trigger
> selects values from table T2, which happens to be in a different
> database. It seems I have to create users in that other database for
> every user who updates or inserts records into T1. Is there any way
> around this, using views or stored procedures, in SQL Server 2000?
> Thanks
> Timo
>

Database triggers - SQL Server - Fields only allowed if listed in another field in another

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).

eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.

Does anyone know the easy way to do this? / Syntax for the trigger?

Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:

Creating and Modifying FOREIGN KEY Constraints

|||

I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)

My statement in('Yes','No','') does not work for my check constraint.

Does anyone know what my syntax would be?

|||A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))

--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')

For mor information, you can refer to:?ALTER?TABLE?

Database triggers

How are the DMLs commited in the database triggers internally as they are based on a table and any table commit will commit all the pending transactions which could lead to problems.
I want to know the flow or algorithm of implicit commit of dbtriggers.
ThanksIn Oracle, at least, there is no implicit commit of database triggers. Any DML performed within database triggers forms part of the same transaction as the triggering statement, and all is either committed or rolled back together when the user issues an explicit COMMIT or ROLLBACK.

database triggers

Hello there
There are triggers for tables that work on table change.
Is there database triggers how work on database changes and database schema
changesIn SQL Server 2005, you now have DDL triggers.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uvpiP18hGHA.4892@.TK2MSFTNGP02.phx.gbl...
Hello there
There are triggers for tables that work on table change.
Is there database triggers how work on database changes and database schema
changes|||Roy
What is the version are you using?
Tom has already answered your question. There is some example how it works
CREATE TRIGGER trg_capture_create_table ON DATABASE FOR CREATE_TABLE
AS
-- PRINT event information For DEBUG
PRINT 'CREATE TABLE Issued'
PRINT EventData()
-- Can investigate data returned by EventData() and react accordingly.
RAISERROR('New tables cannot be created in this database.', 16, 1)
ROLLBACK
GO
For more details please refer to the BOL
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uvpiP18hGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Hello there
> There are triggers for tables that work on table change.
> Is there database triggers how work on database changes and database
> schema changes
>|||Whell Uri
I'm working on sql 2000
can i build triggers for this on sql server 2000?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OcDVuR9hGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Roy
> What is the version are you using?
> Tom has already answered your question. There is some example how it works
> CREATE TRIGGER trg_capture_create_table ON DATABASE FOR CREATE_TABLE
> AS
> -- PRINT event information For DEBUG
> PRINT 'CREATE TABLE Issued'
> PRINT EventData()
> -- Can investigate data returned by EventData() and react accordingly.
> RAISERROR('New tables cannot be created in this database.', 16, 1)
> ROLLBACK
> GO
> For more details please refer to the BOL
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uvpiP18hGHA.4892@.TK2MSFTNGP02.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I'm working on sql 2000
> can i build triggers for this on sql server 2000?
No.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Database Trigger in MSSQL

How can i create database level triggers in MS SQL? i m not talking
about table trigger or view trigger.

Khurram.What do u really want to capture?|||Khurram (khurramanis@.gmail.com) writes:
> How can i create database level triggers in MS SQL? i m not talking
> about table trigger or view trigger.

In SQL 2000 you cannot create trigger on database level. In SQL 2005,
currently in beta, you can.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp