Help with SQL Server Trigger to Truncate Bad Data Before Insert

Help with SQL Server Trigger to truncate bad data before insert

The error cannot be avoided because the error is happening when the inserted table is populated.

From the documentation:
http://msdn.microsoft.com/en-us/library/ms191300.aspx

"The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table."

The only really "clever" idea I can think of is to take advantage of schemas and the default schema used by a login. If you can get the login that the web service is using to reference another table, you can increase the column size on that table and use the INSTEAD OF INSERT trigger to perform the INSERT into the vendor table. A variation of this is to create the table in a different database and set the default database for the web service login.

CREATE TRIGGER [myDB].[mySchema].[TruncDescription] 
ON  [myDB].[mySchema].[myTable] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO [VendorDB].[VendorSchema].[VendorTable]
SELECT SubType, type, substring(description, 1, 255)
FROM inserted
END

SQL trigger on Truncate

From msdn:

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

Truncate table before inserting new data

You can use a TRANSACTION, so your Table is never empty for other queries but this is not the fastest way.

START TRANSACTION;
DELETE FROM your_table;
INSERT into your_table ...
...
INSERT into your_table ....
COMMIT;

or the faster way:

TRUNCATE yout_table;
INSERT into your_table ...
...
INSERT into your_table ....

in this way the TRUNCATE deletes and recreate the table and cant be used in a TRANSACTION, but is really fast.

Create a trigger to run before TRUNCATE TABLE

Did you tried reading the specific document? Triggers applies only for DML statements (INSERT | UPDATE | DELETE) and not for DDL commands (TRUNCATE).

Since you tagged both MySQL and SQL Server

Look at MySQL Documentation; create trigger syntax says

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

Where trigger_event: { INSERT | UPDATE | DELETE }

Look at SQL Server Documentation; create trigger syntax says

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 

As you can see, trigger events are only INSERT | UPDATE | DELETE.
It's not possible using trigger in truncate statement.

Truncate table with a trigger

Below is the snipped using which you can achieve your goal

create table table1 (id int, age number,name varchar2(100));
create table table2 (id int, age number,name varchar2(100));

create or replace trigger sandeeptest after insert on table1 for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'truncate table  table2';
insert into table2(id,age,name) values (:new.id,:new.age,:new.name);
commit;
end;

insert into table1 (id,age,name) values (1,21,'A');
commit;    -- after commit data is persisted in both the tables

insert into table1 (id,age,name) values (2,21,'B');
rollback;-- even after rollback data is presisted in table2 because we created an autonomous trigger

select * from table1;
select * from table2;

How to trigger before INSERT statement in stored procedure in SQL Server 2005?

In the stored procedure itself truncate the table. No need to write a trigger.
Trigger will slow down your process

ALTER PROCEDURE [dbo].[aProcedure]
  @val1 VARCHAR(255),
  @val2 VARCHAR(255),
  ...
AS 
BEGIN
   SELECT @sql = '//Do SELECT statement'

     TRUNCATE table  dbo.MyTable -- < add truncate here
    INSERT INTO dbo.MyTable(....)

    SELECT @paramList = ' /*list of parameters*/';

    EXEC sp_executesql @sql, @paramList, ... parameters
END

SQL Server - Instead of Insert Trigger - override truncation error?

Below is an incomplete idea how to work-around the above problem. It kind of evasive. But interesting way test how far you can rework a database.

The below changes the schema around when a table is created or altered. On creating or altering a table, it will be swapped out for an indexed view after the table gets renamed.

ALTER TRIGGER trigger_CreateTable
    ON DATABASE
    AFTER CREATE_TABLE, ALTER_TABLE
AS
BEGIN
    --SELECT EVENTDATA()

    DECLARE @Prefix AS nvarchar(256) = N'PleaseUseView_'

    DECLARE @Event AS XML = EVENTDATA()
    DECLARE @SchemaName AS nvarchar(255) = (@Event.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)')) 
    DECLARE @TableName AS nvarchar(255) = (@Event.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')) 
    DECLARE @ObjectType AS nvarchar(255) = (@Event.value('(/EVENT_INSTANCE/ObjectType)[1]',  'NVARCHAR(255)'))
    DECLARE @TableWithSchema AS nvarchar(512) = '[' + @SchemaName + '].[' + @TableName + ']'

    CREATE TABLE #SchemaBindingDependencies
    (
        [id] int NOT NULL IDENTITY,
        [schema] nvarchar(256) NOT NULL,
        [name] nvarchar(256) NOT NULL
    )

    INSERT INTO #SchemaBindingDependencies([schema], [name])
        SELECT DISTINCT s.name AS [schema], o.name
        FROM sys.objects AS o
            INNER JOIN sysdepends AS d
                ON d.id = o.[object_id]
            INNER JOIN sys.schemas AS s
                ON s.[schema_id] = o.[schema_id]
        WHERE     o.type ='V' AND d.depid = OBJECT_ID(@TableWithSchema)
              AND SUBSTRING(@TableName, LEN(@Prefix) + 1, 256) LIKE o.[name]

    IF (EXISTS(SELECT 1 FROM #SchemaBindingDependencies))
    BEGIN
        DECLARE @Index AS int = (SELECT MAX(id) FROM #SchemaBindingDependencies)
        WHILE (@Index > 0)
        BEGIN
            DECLARE @ViewName1 AS nvarchar(256) = (SELECT [name] FROM #SchemaBindingDependencies WHERE id = @Index)
            IF (@ViewName1 IS NOT NULL)
            BEGIN
                DECLARE @SchemaName1 AS nvarchar(256) = (SELECT [schema] FROM #SchemaBindingDependencies WHERE id = @Index)
                DECLARE @DropSchemaBoundViewQuery AS nvarchar(1000) = 'DROP VIEW [' + @SchemaName + '].[' + @ViewName1 + ']'

                EXEC(@DropSchemaBoundViewQuery)
            END

            SET @Index = @Index - 1
        END
    END

    IF (SUBSTRING(@TableName, 1, LEN(@Prefix)) <> @Prefix)
    BEGIN
        DECLARE @NewTableName AS nvarchar(512) = @Prefix + @TableName + ''
        DECLARE @NewTableWithSchema AS nvarchar(512) = '[' + @SchemaName + '].[' + @NewTableName + ']'

        EXEC sp_rename @TableWithSchema, @NewTableName

        SET @TableName = @NewTableName
        SET @TableWithSchema = '[' + @SchemaName + '].[' + @NewTableName + ']'
    END

    DECLARE @Columns AS nvarchar(max) = (STUFF((SELECT ',[' + x.[name] + ']' FROM (
        SELECT c.[name]
        FROM sys.columns AS c
            INNER JOIN sys.tables AS t
                ON t.[object_id] = c.[object_id]
            INNER JOIN sys.schemas AS s
                ON s.[schema_id] = t.[schema_id]
        WHERE t.[name] = @TableName AND s.[name] = @SchemaName) AS x FOR XML PATH('')), 1, 1, ''))

    DECLARE @ViewName AS nvarchar(256) = SUBSTRING(@TableName, LEN(@Prefix) + 1, 256)
    DECLARE @ViewWithSchema AS nvarchar(512) = '[' + @SchemaName + '].[' + @ViewName + ']'

    DECLARE @Query AS nvarchar(max) =
        N'CREATE VIEW ' + @ViewWithSchema + N' ' + CHAR(10) + CHAR(13) +
        N'WITH SCHEMABINDING ' + CHAR(10) + CHAR(13) +
        N'AS ' + CHAR(10) + CHAR(13) +
        N'  SELECT ' + @Columns + ' ' + CHAR(10) + CHAR(13) +
        N'  FROM ' + @TableWithSchema + N' '
    --SELECT @Query
    EXEC(@Query)

    SET @Query =
        N'CREATE UNIQUE CLUSTERED INDEX [CIX_' + @ViewName + N'] ' + CHAR(10) + CHAR(13) +
        N'ON ' + @ViewWithSchema + N'(' + @Columns + N')'
    EXEC(@Query)

    -- TODO: Use the below double commented to build a variable insert statement for the "Instead of TRIGGER"
    --
    ----DECLARE @tv_source TABLE (id int)
    ----declare @XML xml;
    ----set @XML = 
    ----  (
    ----  select top(0) *
    ----  from @tv_source
    ----  for XML RAW, ELEMENTS, XMLSCHEMA  
    ----  );
    ----SELECT T.c.query('.'), T.c.value('@name', 'nvarchar(256)')
    ----FROM @XML.nodes('/*/*/*/*/*') AS T(c)
    --
    --SET @Query =
    --  N'CREATE TRIGGER [Trigger_' + @ViewName + N'] ' + CHAR(10) + CHAR(13) +
    --  N'ON ' + @ViewWithSchema + N' ' + CHAR(10) + CHAR(13) +
    --  N'INSTEAD OF INSERT ' + CHAR(10) + CHAR(13) +
    --  N'AS BEGIN ' + CHAR(10) + CHAR(13) +
    --  N'BEGIN TRY ' + CHAR(10) + CHAR(13) +
    --  N' INSERT INTO ' + @TableWithSchema + N'(' + @Columns + N')'
    --  N'   SELECT ' + @Columns + 
    --EXEC(@Query)
END

Ideally, you would use a different schema for tables. And use dbo or
the default for the view.

Once the Instead of TRIGGER is working, you could wrap a TRY/CATCH around it. On the catch, check schema for truncation. And expand the column sizes if needed.

Incomplete solution. But it is the answer I will stick with for now.
If anyone has any better answers or a complete solution, please add it!

tl;dr

One interesting thing is the below query

DECLARE @tv_source TABLE (id int)
declare @XML xml;
select top(0) *
from @tv_source
for XML RAW, ELEMENTS, XMLSCHEMA  

You can return schema. SOAP for the above XML. Or JsonSchema or Avro if using the SQL 2016+ Json version to build Restful API's with schema awareness. And with schema awareness, an application gateway could auto-grab many dispersed Micro-Rest API's into one seemly large Rest API.

---

Related Topics