how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?
Hi:
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
Thanks a lot
The following code example to returns table space usage in my databases, except for AdventureWorks.
When I "USE AdventureWorks", I get the following message. "Invalid object name 'INFORMATION_SCHEMA.tables'."
Why is AdventureWorks different? Thanks. Bill
USE AdventureWorks
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100))
DECLARE @TableName varchar(100)
--Insert Table names into the TABLE variable
INSERT @AWTables
(SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable
EXEC sp_spaceused @TableName
DELETE @AWTables
WHERE SchemaTable = @TableName
END
Hi,
I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).
Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?
Thanks!
Hi,
we've just installed SQL2005 and, as expected, are hitting problems with collation settings.
We have made sure that the collation of our new server is the same as the old (working) SQL2000 server (both are Latin1_General_CI_AS) but a function that works fine on the 2000 box just doesn't work on the 2005 box.
The problem is this...
Code Snippet
select *
from information_schema.[tables] as t
left join :: fn_ListExtendedProperty('MS_Description','user','dbo','table',null,null,null) as xp on ( xp.objname = t.table_name )
...where that string comparison on "table_name" results in...
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."
Now... I can't see where the "Latin1_General_CI_AI" is coming from. The two data sources are both "system" sources, so should, I would have thought, been the same. If I do...
execute sp_help "information_schema.tables"
...all the columns are Latin1_General_CI_AS. If I do...
execute sp_help fn_ListExtendedProperty
...the same collation is returned for all columns.
I've checked everything I can think of (server, master, tempdb, etc.), and everything seems to be "AS".
Where might this "AI" collation be defined ? We have only just installed 2005, so a re-install, while a pain, is not a massive problem, but what should we check/change ?
Thanks
I think cursors might help me, but I'm not sure. I'm looking for ideason how to solve a problem I have.Consider two tables, one table contains student information (very wide100 fields) , the other historical changes of the student information,(narrow, just fields that record changes).As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,RECORD_DT and has one student in it.Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2records, since the student changed their major 2 times.I want to end up with a table the contains 3 rows, the 2 changes to theMajor and the current student record. I want each row to be complete.Everything that I have tried (joins, outer joins, union) I end up withsome field being null (in my example, the STUDENT_NAME would on be inthe original row, and null for the two changes)I know this is pretty vague, but I am wondering if this is a place touse CURSORS?(Some of you may recognize this as a type 2 dimension or slowlychanging dimension as used in a data warehouse, which it is. I need tobuild up my historical changes to I can feed it to my warehouse. I havethe current student record, and all the descreet changes made to thestudent.)TIARob
View 18 Replies View RelatedI'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...
I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.
I'd rather pay an expert here than try to navigate a tech help line.
ConnIS is defined in an earlier include file...
Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = "dbo.Expandset" 'Name of SP
oCmd.CommandType = adCmdStoredProc 'ADO constant for 4
Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
Response.Write oRs.RecordCount & "<hr>"
oRs.Close
Set oRs=Nothing
This code generates the following result when run from an active server page:
-1<hr>
When I execute the raw SQL code ("exec Expandset 892") against the stored proc in the query analyzer, I get:
item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2
The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------
The relevant portions of the Table definitions for SITE_Container are:
CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)
-------------------------------------------------------------------
My contact information is:
Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax
I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.
/*
Original by: Bill Graziano (SQLTeam.com)
Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40)
*/
declare @low int
select @low = low from
master.dbo.spt_values
where number = 1 and type = 'E'
select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved,
(d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp,
((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused
from
sysobjects o
inner join
(select distinct id, rowcnt
from sysindexes
where keys is not null and first != 0) ro on o.id = ro.id
inner join
(select id, sum(reserved) reserved
from sysindexes
where indid in (0, 1, 255)
group by id) r on o.id = r.id
inner join
(select c.id, dpages+isnull(used, 0) data from
(select id, sum(dpages) dpages
from sysindexes
where indid < 2
group by id) c full outer join
(select id, isnull(sum(used), 0) used
from sysindexes
where indid = 255
group by id) t on c.id = t.id) d on r.id = d.id
inner join
(select id, sum(used) used
from sysindexes
where indid in (0, 1, 255)
group by id) i on d.id = i.id
where o.xtype = 'U'
order by reserved desc
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
Hello, everyone. I am sorry I haven't been around for long but I have got this confusion. I haven't been able to set triggers between 2 tables.
I have got a set of data in 1 table and another set in the other table. I want to set the trigger by comparing the data column from one table to another.
All I want to know is is ti possible to do it? Because I have been told that I can only set triggers on only one table.
Any help is much appreciated.
Thanks in advance.
I'm thinking of building a trigger against a system table(sysobjects) in
database(a) on server(a) that will assist me in updating a table in
database(b)on server(b). What I need to know from table(b)is if a new table
has been added and removed from database(a) on server(a). I want to use a
store procedure to query the table(b). I was thinking of building a trigger
against my sysobjects table that would update table(b) whenever any tables
are added or removed from database(a). Has anyone built triggers against
system tables? I am running SQL 7.
Dear Group,I would like to create an audit table that is created with a trigger thatreflects all the changes(insert, update and delete) that occur in table.Say I have a table withSubject_ID, visit_number, dob, weight, height, User_name, inputdateThe audit table would have .Subject_ID, visit_number, dob, weight, height, User_name, inputdate,edit_action, edit_reason.Where the edit_action would be insert, update, delete; the edit_reason wouldbe the reason given for the edit.Help with this would be great, since I am new to the world of triggers.Thanks,Jeff
View 1 Replies View RelatedHi,
I have decided to use CLR Triggers to synchronize data between two different tables but I have a problem. The scenario is this. I do an insert in Table1. Table1 firest the trigger which then inserts a record in Table2. The trigger in Table2 fires a trigger which does an insert to Table1 again.
I need to find a way to disable the trigger so that it will not go in an infinite loop when I insert a record in either of the tables. What I have done so far is to disable Table2's trigger before I commit my insert to Table2 so that Table2's trigger won't fie. I will then enable the trigger after the insert is committed. Also, I have a webservice which manages the inserts/updates/deletes. I call webmethods from this webservice in my clr triggers.
Help please!
Thanks,
Ryan
How can we write triggers on system table dbo.sysobjects,
when i tries to write a trigger, it is giving an error that permission is denied.
I even give a permission of "allow modifications to system catalogs" in Enterprise manager.
still it is not giving permission.
How can i create a trigger on dbo.sysobjects table?
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
View 2 Replies View RelatedHi,
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Thanks
Rob
I am reading the WROX "Professional SQL Server 7 Programming" book.
The following code appears on page 424:
CREATE TRIGGER ProductIsRationed
ON Products
FOR UPDATE
AS
IF EXISTS
(
SELECT 'True'
FROM Inserted i
JOIN Deleted d
ON i.ProductID = d.ProductID
WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
AND d.UnitsInStock - i.UnitsInStock > 0
)
BEGIN
RAISERROR('Cannot reduce stock by more than 50%% at once.',16,1)
ROLLBACK TRAN
END
The trigger fires when an UPDATE is made to Products table. The author states that the Inserted and Deleted tables only exist for the life of the trigger, not before, and not after the trigger runs. If this is true, then why would there be any rows in the Deleted table in this case? No rows were
deleted within the trigger. As far as I can see, no rows have been updated
either. If the condition does exist, no rows will be updated, and an error will be displayed. Otherwise, the row will be updated. Then there would be a row in the inserted table. But then the trigger is finished and the inserted table for that trigger disappears. I think my logic is flawed, which is why I am writing. I don't think I fully understand the Inserted and Deleted tables.
Any help would be greatly appreciated.
Nathan
I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".
My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.
Gazzou
Hello,
I am trying to create a stored procedure that will disable triggers on any given table. Basically I want to pass in a table name as a parameter and query the master table for all triggers that belong to that table...then disable them. I just don't know how to get a list of triggers that belong to a table?
Thanks,
Andrew
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies View Relatedi'm in a bit of a bind at work. if anyone could help, i'd greatlyappreciate it.i have a web app connecting to a sql server using sql serverauthentication. let's say, for example, my login/password isdbUser/dbUser. the web app however, is using windows authentication.so if I am logged into the network as 'DOMAINEric', when I access myweb app, my web app knows that I am 'DOMAINEric'. but to the sqlserver db, I am user 'dbUser'.now, i for each table i have, i need to implement an audit table torecord all updates, inserts, deletes that occur against it. i wasgoing to do so with triggers. this is all fine for selects, inserts,and updates. for each table, i have an updatedby and an updatedate.for example, let's say i have a table:create table blah(id int,col1 varchar(10),updatedby varchar(30),updatedate datetime)and corresponding audit table:create audit_blah(id int,blah_id int,blah_col1 varchar(10),blah_updatedby varchar(1),blah_updatedate datetime)for update and insert triggers, i can know what to insert into theupdatedby column of audit_blah because it's in a corresponding row inblah. my web app knows what user is accessing the application, andcan insert that name into blah. blah's trigger will then insert thatname into audit_blah.however, in the case of a delete, i'm not passing in an 'updatedby',because i'm deleting. in this situation, how can the trigger knowwhat user is deleting? the db only knows that sql user 'dbUser' isdeleting, but doesn't know that 'dbUser' is deleting on behalf of'DOMAINEric'. is there any way for my app to inform the trigger toaccess my windows identity without having a corresponding row in thetable from which to pull that info?obviously, i could have each of my app's users log into SQL serverthrough Windows authentication; then i could just use SYSTEM_USER.but let's say, for performance's sake, it'd be better for me to useone sql server login. (i believe one user works better for connectionpooling purposes.) is there a way to get around this?(i'm hoping a built-in function exists that solves all my problems.)suggestions? resources?any help would be great appreciated.happy turkeys.Eric
View 2 Replies View Related
I'm know the above tables are system generated, am I right in assuming the following.
1) The tables are unique to the current user.
2) The tables only last as long as the transaction that caused their creation
Thanks in advance
Alex
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
begin
declare @bid as int
@bid = select MAX(BorrowerID)
FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID)
values (@bid)
end
GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
INSERT Table_SoldProgression(BorrowerID)
values (select MAX(BorrowerID)FROM Table_Borrower)
GO
I want to compare the before and after values of an UPDATEd column using a trigger. I want to know if the value in the column has changed. Simple? No!
As you know, SqlServer puts the before image of the UPDATEd rows into the DELETED virtual table and the after image of the UPDATEd rows in the INSERTED virtual table.
So you would get the before and after data by doing a SELECT on these tables.
But here is the problem - how do you join the tables? What if there are >1 rows in these 2 tables (because the UPDATE affected >1 rows) - how do i know which "old"/DELETED rows correspond to which "new"/INSERTED?"
Ok - I could join the 2 tables on the primary key, but what if the primary key was updated? In that case the join would not work - the DELETED table would contain the old primary key value and the INSERTED table would contain the new (different) primary key value. In fact, ALL of the columns may have been changed by the UPDATE.
Now, there is another thing to try with triggers - the
IF UPDATE ( <columname> )
test. This is designed to tell you if a specified column was UPDATEd by the last UPDATE. However, this will return TRUE for any UPDATE that mentions the column - even if the UPDATE does not change any data! So I cannot determine whether a certain column has had its value changed with this either.
So then you can try another test mentioned in the docs for CREATE TRIGGER - the
IF COLUMNS_UPDATED()
test. However, this will report that a column has been updated, NOT whether the data has changed as aresult of that UPDATE.
So if you UPDATE the value in the column to the same value as it was beforehand (admittedly, a pointless thing to do, but it could happen in some apps), this fuction will say, yes, this column was updated.
So my question remains - how do I know if the data has changed in a column after an UPDATE, using a trigger?
Any ideas?
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS
DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''
DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)
DECLARE CurHistoryTable CURSOR FOR
-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0
BEGIN
-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
END
SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END
IF @AllowNulls = 'Y'
BEGIN
SET @SQLTable = @SQLTable + ' NULL'
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ' NOT NULL'
END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END
CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF
SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table
EXEC(@SQLTable)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
END
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies View RelatedI want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
Thanks for help.
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies View RelatedI am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script database
Once that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts
The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.
As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL
The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.
Hi to all,
I'm using SQL Server 2005. May I know where the INFORMATION_SCHEMA
is found, can we view this by object explorer.
Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
Thanks
Hi:
I need to change a column default to '' and not null for 1500 databases accross 10 servers.
if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end
Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?
thanks
-D
I'm having a really hard time displaying the structure of my database using INFORMATION_SCHEMA. Can this be used with Access? I've used it before for sql databases but when I try it with this access one I get an error message telling me: Can't find file c:...INFORMATION_SCHEMA.mdb
I'm hoping maybe there's another command that works just as well or some other way to get around it.
I'm using Visual Web Developer 2005 Express
and my .aspx pages are coded using VB so any insights would be awesome!
Thanks much,
~UNI